IFERROR in Data Validation Formula

Rafaa

New Member
Joined
Aug 29, 2013
Messages
23
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
=IF(E4<>"",...,...)
 

Rafaa

New Member
Joined
Aug 29, 2013
Messages
23
=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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,594
Messages
5,625,696
Members
416,127
Latest member
MALEPINZON

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top