creating drop down lists from source on another page

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

Hi Shaun


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

HTH
Cheers
Carim

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" wrote in message
...
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

thanks for your time

It was helpful

"Carim" wrote:

Hi Shaun


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

HTH
Cheers
Carim

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

"Tezza" wrote:

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" wrote in message
...
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

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

"Tezza" wrote:

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" wrote in message
...
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

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
--
mb_bajaj


"Carim" wrote:

Hi Shaun


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

HTH
Cheers
Carim

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

On Wed, 10 Mar 2010 22:24:01 -0800, mb_bajaj
wrote:

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