creating drop down lists from source on another page

[Shaun]

Using Data/Validation trying to create a list based on our headcount stored
on another tab. However Excel telling me impossible.

Can I please have some suggestions on how to create list - I need the exact
names to be able to do Lookups

Thanks in advance

 

[Carim]

Hi Shaun


Once you have Insert Name Define,
Use Data Validation Allow List Source =YourName
do not forget sign =

HTH
Cheers
Carim

 

[Tezza]

Shaun

1. Go to your list on the other tab and create a named range for it
likethis:

a. Highlight all the cells (in 1 column only) that you want included in your
dropdown list

b. Enter a valid name (spaces and some punctuation characters aren't
permitted) for the range in the 'name box' (found to the left of the formula
bar, probably displaying the address of the currently active cell).

2. Highlight the cell(s) that you want to contain the validation/dropdown
list

3. From the data menu select validation

4. On the Settings tab, select 'List' from the 'Allow' selection

5. Where says 'Source' don't attempt to select your range, but type in "="
and the range name that you used in step 1. For example, "=HeadcountList"

I hope this solves your problem.

Terry Rees

 

[Shaun]

thanks for your time

It was helpful

 

[Shaun]

Terry,

thanks very much. I was trying to be too clever and add a dynamic range so
when new headcount were added, list would auto-populate. Removed that and it
works great.

Thanks

 

[Kita]

These instructions were very helpful for me too. Thank you for taking the
time to help me as well!

 

[mb_bajaj]

Hi Carim

I wanted to use the validation function while my list is in another
workbook. I followed the same procedure, but did not help me. I am not sure
where m I doing some thing wrong?

Thanks

 

[Gord Dibben]

See Debra Dalgleish's site for instructions.

http://www.contextures.on.ca/xlDataVal05.html

Note this part...................

For data validation to work, the workbook which contains the list must be
open, in the same instance of Excel. You could create the list in a workbook
that is always open, but hidden, such as the Personal.xls workbook.



Gord Dibben MS Excel MVP