Data validation

[nostrum]

I am trying to create a formula which shows a different drop down list depending on the value of another cell, and basing this on vlookup


Example:

IF(A1=one, VLOOKUP(B1,tableone,4,TRUE),IF(A1=two, VLOOKUP(B1,tabletwo,4,TRUE),IF(A1=three, VLOOKUP(B1,tablethree,4,TRUE),IF(A1=four, VLOOKUP(B1,tablefour,4,TRUE)

The problem I have is A) the source box is so small it makes it very awkward seeing what you have inputted, and B) its quite long winded!

Does anyone have any suggestions on how I can make this easier to work with please?

I would upload but it is a huge spreadsheet!

Thanks in advance

Sam

[JieJenn]

Maybe the link might help

http://www.excel-easy.com/examples/d...own-lists.html

[simarui]

one thought - putting one, two, three and four in a range (i used c6:f6) and the table names in another range (i used c7:f7) this seems to be working:

Please Login or Register  to view this content.

[nostrum]

Thanks for the replies.

The guide you posted a link to seems to work great, one problem though!

The information in the tables (named range) is subject to change. If I change it, when I revert back to the drop down box, the information in it this hasn't, i:e it still displays the information as before?

Any ideas why this is?

Thanks in advance

[JieJenn]

Since the list is link to the name range, any change you make will reflect on the dropdown list.

[nostrum]

Yes, you are right.

The issue is that the name range information cells are taken from another sheet by using ='anothersheet'$A$1, it is this section which isn't working correctly. I need to manual re-enter the formula to 'refresh'

Any ideas how I can perform the same action, but which will automatically update?


Thanks again

[JieJenn]

Can you post your file?