Can the source in data validataion be located in another sheet?

[Guest]

If I am creating a drop down list in Excel using Data Validation, can the
source part be located in another worksheet? I would like to put a
highighted range in the source box instead of typing in values/text. Also,
if I add items to the original list on the other worksheet or edit them in
any way, will my list box reflect this update?
Thanks!

 

[Peo Sjoblom]

Yes, but you need to name the list, assume it is in Sheet2 A2:A50, go to
Sheet2, select A2:A50 and in the name box type in a name like List1 or
something (or select the range, then do insert>name>define and type in the
name)
then refer to it as

=List1

in the list source box

 

[Gord Dibben]

Peo addressed the first part of your question, but to add items to the list you
would have to make it a dynamic range.

From Debra Dalgleish's site..........................

Some lists change frequently, with items being added or removed. If the list is
the source for a Data Validation dropdown, use a dynamic formula to name the
range, and the dropdown list will be automatically updated.

For instructions, view this page:

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP