VLOOKUP and INDIRECT, TEXT formula combination

Question

Here is perfectly working formula I have combines with the help of Alistair McEvoy and idfurw:

=HYPERLINK(VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/","08/16!A2:Q26"),6), VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/","08/16!A2:Q26"),9))

However, I want to update it. Especially this part: "08/16!A2:Q26" from above formula.

How do I re-write formula so that instead of 08/16 would be value from cell A1. Cell A1 contains 08/16 (date format).

This formula gets me data from different unopened google sheet. Unopened spreadsheet contains sheet named 08/16 where data should be extracted from.

I tried this combination but it did not work:

=HYPERLINK(VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",INDIRECT("'"&TEXT($A$1,"MM/dd")&"'!A2:Q26"),6), VLOOKUP(A4,IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",INDIRECT("'"&TEXT($A$1,"MM/dd")&"'!A2:Q26"),9))

Probably I am doing mistakes with ' and "

So I want to transform "08/16!A2:Q26" into --> "08/16!A2:Q26" but instead of 08/16 will be value from A1 cell. A1 cell value is 08/16

Answer 1

try:

=HYPERLINK(
 VLOOKUP(A4, 
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 A1&"!A2:Q26"), 6), VLOOKUP(A4,
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 A1&"!A2:Q26"), 9))

or:

=HYPERLINK(
 VLOOKUP(A4, 
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 TO_TEXT(A1)&"!A2:Q26"), 6), VLOOKUP(A4,
 IMPORTRANGE("1RnkAkydjc_wY_iLyyfktUucvi9MHmneVTL9H9J3bgss/",
 TO_TEXT(A1)&"!A2:Q26"), 9))