Reference a range to a cell

Question

Hi Need help to refer a range to a cell value (which has the range).

QUESTION: How to refer the range to a cell in J84 for B86:B146 and cell J85 for D:I using INDIRECT?

Here's my current formula:

={"HOURS"; 
 ARRAYFORMULA(IFNA(IF(VLOOKUP(B86:B146, Sheet1!D:I, 6, 0)="-", "-", TEXT(TIME(
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B146, Sheet1!D:I, 6, 0), "(\d+)h")), 
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B146, Sheet1!D:I, 6, 0), "(\d+)m")), 
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B146, Sheet1!D:I, 6, 0), "(\d+)s"))), "[hh]:mm:ss"))))}

Sheet: https://docs.google.com/spreadsheets/d/1c3HdMq4PA50pYr88JqPoG51jvru8ipp8ebe4z5DczTQ/edit?usp=sharing

INDIRECT does not normally work under ARRAYFORMULA, but it can sometimes be manipulated to achieve what you want. Please share your sheet to make it editable by all, to let us help you. — kirkg13

JPV JPV · Accepted Answer · 2020-08-17T12:16:54

See if this helps

={"HOURS"; 
ARRAYFORMULA(IFNA(IF(VLOOKUP(INDIRECT(J84),INDIRECT("Sheet1!"&J85), 6, 0)="-", "-", TEXT(TIME(
IFNA(REGEXEXTRACT(VLOOKUP(INDIRECT(J84), INDIRECT("Sheet1!"&J85), 6, 0), (\d+)h")), 
IFNA(REGEXEXTRACT(VLOOKUP(INDIRECT(J84), INDIRECT("Sheet1!"&J85), 6, 0), "(\d+)m")), 
IFNA(REGEXEXTRACT(VLOOKUP(INDIRECT(J84), INDIRECT("Sheet1!"&J85), 6, 0), "(\d+)s"))), "[hh]:mm:ss"))))}

Note: if you'd have the value 'Sheet1!D:I' in cell J85 you can change the second indirect to INDIRECT(J85)...

Reference a range to a cell

1 Answers