CODEWITHSUNDEEP
excel
Mika
Mika

Reputation: 1479

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

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.

Edit2: As a general rule of thumb, I noticed that everything works better if I put the logic into a named range and refer the named range in data validation formula. Some functions just won't work in data validation formula, but they work in named range.

Upvotes: 1

Views: 2039

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9930

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 this spreadsheet for an example.

Relative Named Range

Named ranges are mostly used to identify locked or specific ranges. But you can use them to create relative references. Example in above spreadsheet is a relative named range of oneCellBelow. This is a named range that will always reference the cell below it. To make this, see the below screenshot that has the selected cell of H5 but has a reference to H6 without the absolute $. This will result in always referencing the cell below it.

Update Based On Comments To use a relative named range of one cell to the right, the below screenshot should Refer to: Sheet1!i5 instad of Sheet1!H6.

enter image description here

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

enter image description here

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.

Upvotes: 1

Jos Woolley
Jos Woolley

Reputation: 9062

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)

Upvotes: 2

Related Questions