Data Validation Source=INDIRECT errors

Hi All,

I have a template where I need one drop down list to inform multiple different dropdown lists. The first cell will select either "present" or "absent". There will then be multiple cells with different information in each. For example, present will inform the selections for extent (few occurrences or widespread) and also record quality (confirmed by survey, unconfirmed), and absent a different set of options per variable.

To achieve this, I am employing a combination of INDIRECT and VLOOKUP in data validation, using this formula:

=INDIRECT(VLOOKUP( J2, RecordQLookup, 2, FALSE))


where J2 is the initial present/absent selection and RecordQLookup is a 2by2 named table with Present/Absent in one column and the names of the ranges containing the data for the drop down lists for that cell in the second

This formula works within a cell but not in the source for data validation, returning the error message "there's a problem with this formula" (like a typo).

If I place quotation marks around RecordQLookup, the formula is allowed, but returns a "the source currently evaluates to an error" message.

It seems like data validation source has some trouble processing tables and lookup, I have tried googling but to no avail. Does anyone have any experience with this? Beginning to drive me a bit mad! Happy to provide a dummy example if need be


Thanks in advance

Kathryn