Extract cell on a different worksheet indirectly via a heading cell

Question

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

hansvogelaar · Accepted Answer

Jack Miller&nbsp;
INDIRECT uses A1-style referencing by default. To use R1C1-style, specify FALSE as second argument:
&nbsp;
=VLOOKUP(RC1,INDIRECT("'"&amp;R1C&amp;"'!C3:C4",FALSE),2,FALSE)

Forum Discussion

Extract cell on a different worksheet indirectly via a heading cell

Share

Resources