IFERROR in Data Validation Formula

[Rafaa]

Hi there,

I would like to provide a dropdown list based on product categories for specific customers. Each customer has its own table, and if a customer doesn't have a specific table set up, formulas in the worksheet refer to a table "StandardTable".

I've tested the following formulas in Data Validation and they worked separately.

=INDIRECT("StandardTable[CATEGORY]") *cases where the customer doesn't have a specific table for its product categories

=INDIRECT(E4&"Table[CATEGORY]") *cases where the customer (in column E - E4 in this example) has its own table Ex: CustomerTable

I would like to combine these two formulas to obtain a dropdown list from the StandardTable when a customer table cannot be found based on the customer entered in E. This happens when a new customer is listed, for example.

I tried the following:
=IFERROR(INDIRECT(E4&"Table[CATEGORY]"),INDIRECT("StandardTable[CATEGORY]"))

I tested this combined formula in a cell and it works, with SPILL errors of course, but when I entered it in Data Validation, the system warns that this source returns an Error and accepts the formula, but the dropdown lists don't work.

I was wondering if you guys could shine a light on the type of alterations I need to make to the formula for it to work in the Data Validation feature in Excel.

p.s. I am using Excel for Mac.

Please let me know if you guys need any further details.

Kind regards,
Raf

 

[mart37]

=IF(E4<>"",...,...)

 

[Rafaa]

=IF(E4<>"",...,...)

Hi mart37,

Thank

E4 might not be empty and still refer to a customer that doesn’t have its own table, which would require the validation to point to the Standard table.

I might trying the IF function replacing E4<>””writh VLookup(E4, CustomersList,1)<>””

Cheers,
Raf