thanhvanchi
12-10-2016, 12:11 AM
Hello everyone!
I have a spreadsheet (not of my own making so options are limited - no VBA) where I'm trying to allow users to make some choices using Data Validation dropdowns.
Users can choose a product, they then choose a sub-product, then a currency and then a timescale. I have various tables containing lists of products, sub-products, currencies and timescales - using Data Validation and Indirect to make each table dependent on the previous selections.
This works fine for the Product and sub-Products but the currencies are causing me a problem.
There is one table of 'main' products and 4 sub-Product tables. However, there are numerous possible currency tables.
All tables etc are held in a separate worksheet.
Using a lookup I can work out which currency table will match the sub-Product chosen, but I cannot seem to have the result (i.e the currency table data) appear in the next level validation for the user to make the next choice. I have tried using Indirect with a reference to the lookup result but this produces an error ("the list source must be a delimited list...") or simply produces one result - the name of the lookup range itself. All ranges containing data are Named Ranges.
I'm not sure where I'm going wrong here and would be grateful for any pointers.
Thank's a lot!
I have a spreadsheet (not of my own making so options are limited - no VBA) where I'm trying to allow users to make some choices using Data Validation dropdowns.
Users can choose a product, they then choose a sub-product, then a currency and then a timescale. I have various tables containing lists of products, sub-products, currencies and timescales - using Data Validation and Indirect to make each table dependent on the previous selections.
This works fine for the Product and sub-Products but the currencies are causing me a problem.
There is one table of 'main' products and 4 sub-Product tables. However, there are numerous possible currency tables.
All tables etc are held in a separate worksheet.
Using a lookup I can work out which currency table will match the sub-Product chosen, but I cannot seem to have the result (i.e the currency table data) appear in the next level validation for the user to make the next choice. I have tried using Indirect with a reference to the lookup result but this produces an error ("the list source must be a delimited list...") or simply produces one result - the name of the lookup range itself. All ranges containing data are Named Ranges.
I'm not sure where I'm going wrong here and would be grateful for any pointers.
Thank's a lot!