Glaswegian
07-21-2016, 06:37 AM
Hi all
I'm looking for some help with the above.
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.
I'm looking for some help with the above.
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.