PDA

View Full Version : Data Validation / Indirect help



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.