SOLVED

Extract cell on a different worksheet indirectly via a heading cell

Occasional Contributor

Screenshot_8.jpg

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)

JackMiller_0-1656446173960.png

I would most definitely appreciate your guidance,

JackMiller_1-1656446549261.png

Jack

2 Replies
best response confirmed by Jack Miller (Occasional Contributor)
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)

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