Can't use reference to another sheet in Data|Validation|Allow List?

[Chip Pearson]

Hutch,

Create a defined name pointing to the validation list, and use the
name in the list source property.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Max]

Try this:

Select A1:A10 on sheet 1 (your list for the DV)
Click within the namebox
(the one with the drop arrow just to the left of the formula bar)
Type a name for the list: MyRange (for example)

In the DV dialog, settings tab:
Under Source, put: =MyRange
(instead of : =Sheet1!$A$1:Sheet1!$A$10)

 

[Gord Dibben]

Hutch

Select your A1:A10 range on Sheet1. On menubar select Insert>Name>Define.

Click Add button then type in a name such as "MyList"(no quotes).

Down in the "refers to" box you will see the range it refers to. In this case

it would be =Sheet1!($A$1:$A$10)

As Chip points out, use that name(MyList) in your DV o Sheet2.

Source is =MyList

You can type in =MyList or Insert>Name>Paste and select MyList.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[hutch]

How can I do this...?
I wish the user to input data on Sheet1
but in Sheet2 use Data|Validation|Allow List to create a list of what is
input on sheet1.

=Sheet1!$A$1:Sheet1!$A$10

I get an error when I try this...
is there a way to do this?

 

[hutch]

I saw that... tried it but couldn't get it to reference another sheet for
the list.
maybe I missed something?
How do I do the name in the name box so that it makes it a Dynamic range?
do I put this:
=OFFSET($A$1,0,0,COUNTA($A:$A)
in the name box on sheet1?
then how do I add the named reference?

Sorry, I am so much of a dummy... excel is just starting to make sense to
me...