Jul 04 2022 07:01 AM
Hi,
I want to be able to retrieve a value from a cell, which holds a letter, and to use this letter as the column-letter in my IF-function. E.g. I want the "D" in D10 in this function =IF(B10>'Datasheet_1'!D10;"YES";"NO") to be a variable that depends on the letter in another cell. So if my cell was to say "E" instead of "D" the function would represent this function =IF(B10>'Datasheet_1'!E10;"YES";"NO").
Is this doable?
Jul 04 2022 07:11 AM
SolutionYou can use the INDIRECT function for this purpose. Let's say the column letter is in A1.
=IF(B10>INDIRECT("'Datasheet_1'!"&A1&"10");"YES";"NO")
Jul 04 2022 08:51 AM
The business logic doesn't exactly shine through this proposed test. There must be better ways of selecting a value for comparison than synthesising direct cell references within a volatile function. For example, it might be possible to pick up the comparison cell using XLOOKUP, or even HLOOKUP on legacy versions of Excel.
Jul 04 2022 11:59 AM
We'd have to know more about the setup.
Jul 04 2022 11:24 PM
Jul 04 2022 07:11 AM
SolutionYou can use the INDIRECT function for this purpose. Let's say the column letter is in A1.
=IF(B10>INDIRECT("'Datasheet_1'!"&A1&"10");"YES";"NO")