Excel97 - Data Validation List Source 1

[JudyAH]

Hi,

I'm using the data validation tool in Excel to restrict valid inputs to the values in various named ranges. The named range to be used depends on the value of another cell. This value is actually the name of the range. For example if the cell value (say, B2)is the string "Colours", the list source is =Colours which is the named range containing valid colours. If the value of this cell (B2) is changed to "Shapes", I need the list source to be =Shapes, therefore refering to the named range containing valid shapes. Hence I need to set the list source to the value of B2, not B2 itself.

It may be a stupidly simple question - but I just cannot think of an answer. I'd greatly appreciate any assistance.

Thank you
Judy.

 

[JVFriederick]

I think this can be done. The trick is to always refer to the same range name within the validation cells. For example, "=Validate".

Now within the validation list itself, write formulas that change each cell based upon the trigger "Colours" or "Shapes". For two types of lists you will need a total of three ranges : Validate, Colours, and Shapes. The list in Validate refers to either Colours or Shapes.

Let me know if you need any more help.

 

[JudyAH]

Thank you JVFriederick. Yes I do need more help please. Where should I place the formula, and what should the formula be?

 

[JVFriederick]

Send me your Email address and I'll send an example file.

 

[JVFriederick]

Send an Email to JVFriederick@Yahoo.com and I'll send a file that you can use.

 

[JudyAH]

Thanks. It's my first name concatenated with "honan", domain is hotmail. (Couldn't see how I could send it direct to you)

 

[JVFriederick]

On it's way.

 

[JudyAH]

It worked! Thank-you!