Extract cell on a different worksheet indirectly via a heading cell

[Jack Miller]

I'm using Excel 2016 and would like to use the Heading "220625 AC" in the formula to fetch the time from the worksheet instead of modifying the formula in each column.

=IF(ISNUMBER(VLOOKUP(RC1,'220625 AC'!C3:C4,2,FALSE)),VLOOKUP(RC1,'220625 AC'!C3:C4,2,FALSE),"")

The sixth line is a simplified test case which fails in the sixth step of evaluation. I don't understand where it gets R3C3:R4C3.

=VLOOKUP(RC1,INDIRECT("'"&R1C&"'!C3:C4"),2,FALSE)

I would most definitely appreciate your guidance,

Jack

[#2 · Accepted solution]

@Jack Miller 

INDIRECT uses A1-style referencing by default. To use R1C1-style, specify FALSE as second argument:

=VLOOKUP(RC1,INDIRECT("'"&R1C&"'!C3:C4",FALSE),2,FALSE)

[#3]

Clogs, aye? Love it! Thanks, too obvious.