Data Validation

[Dave Eade]

I want to base my data validation on a list on another worksheet within the
same workbook - excel doesn't seem to want me to do this?
Is there any way round referencing the cells on the other worksheet?

 

[Luke M]

you could create a group of cells on your current sheet that reference the
other sheet (a simple ='Sheet2'!A2 type formula), and hide them (maybe
IV2:IV100?)
and then have your data validation base its list off of that.

 

[Dave Eade]

I'be already done that, it's ok but untidy - i'd prefer the data to be kept

 

[Luke M]

Found a solution!

Select the lsit of cells in your second sheet, and name this range. (Upper
left where cell address is normally displayed, type a name, e.g. MyRange, and
confirm with Enter).

Now, for your data validation, select list, and then input
=MyRange

 

[Tanya M]

It seems to me I've gotten around that by naming my list and using that range
name. Be sure to enter the range name with an equal sign... i.e. =Range