Excel data validation to use list based on value in other cell using indirect reference-CodePudding

If I put =IF(B1="My Value";ListA;ListB) or =IF(INDIRECT("B1")="My Value";ListA;ListB) into a data validation on cell A1, it works. But if I put =IF(INDIRECT(ADDRESS(ROW();COLUMN() 1))="My Value";ListA;ListB) it throws an error.

Does anyone have idea why it works like that?

Edit: The reason why I need it to be indirect and relative to the cell is that people that use the spreadsheet can copy and paste values in the spreadsheet, but I need the references to stay untouched.

CodePudding user response：

Ah, yes, now I recall. For some reason if the reference passed to INDIRECT is not static, then it needs to be stored as a Defined Name in order to be passed to a Data Validation list.

So you will need to define MyCheck (say) as:

=INDIRECT(ADDRESS(ROW(A1),COLUMN(A1) 1))

(It goes without saying that you need to create this Name whilst the active cell in the worksheet is A1.)

After which the Data Validation List formula is:

=IF(MyCheck="My Value",ListA,ListB)

CodePudding user response：

I don't know WHY it works like that, but my best guess is that it has something related to using row() within a cell that doesn't exist. However, there is a workaround to what you're trying to do by creating a specif RELATIVE named range and making that part of your validation. This results in exactly the same thing as what your failed formula is trying to accomplish.

See

Here's an image of what it should look like when completed:

Validation

As I said above, I don't know why using the relative named range works, and the Row() indirect does not, but you can use the above named range to create your relative validation.

Using your example, the validation formula could be written as:

=IF(oneCellBelow="My Value";ListA;ListB)

Hopefully that gets the end result you need. In the example file you can see two different cells using the same validation, but with different results.