CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
KB John
KB John

Reputation: 89

Reference a range to a cell

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

Upvotes: 0

Views: 80

Answers (1)

JPV
JPV

Reputation: 27292

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)...

Upvotes: 1

Related Questions