Is there a way to reference 2 separate worksheets with validation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Erik - 12 Jun 2007 20:17 GMT

Ive been playing around the list command in validation and I was wondering if
there was a way to use ranges from 2 different worksheets to be the source to
select from. I've tried using the name box command to name the cells and
create a union, and that wont work, and something like the 3-D command wont
work because I've already programmed a few macro's that are referencing those
cells.

Reply to this Message

pdberger - 13 Jun 2007 19:25 GMT

Erik --

I've been trying to solve the same problem. The only way I could do it was
a fairly crude "brute force" work-around. On the page where I had the
validation function, I set aside (and hid if necessary) a section that
automatically copies ("=Sheet2!A1, etc.) the validation list from the other
worksheet. I set it up to copy a lot more cells than I have filled in on the
source worksheet, so that I can add to the validation list later and not have
to worry if I remember to update the list on the 'target' sheet. Then I use
the copy on the 'target' sheet as the data validation list.

It would be nice to see a more elegant solution -- hopefully both of us will
hear from someone smarter than me.

HTH

> Ive been playing around the list command in validation and I was wondering if
> there was a way to use ranges from 2 different worksheets to be the source to
> select from. I've tried using the name box command to name the cells and
> create a union, and that wont work, and something like the 3-D command wont
> work because I've already programmed a few macro's that are referencing those
> cells.

Reply to this Message