Change value in formula with a dynamic link

Is there a solution to get the value $27 from a cell where this value is part of the formula. (see example) I have a whole list of the same formulas as this example. where only A4 is different. In all these formulas I only want to change the number 27. At the moment I do this with a filter. I would prefer to see the formula extract the number 27 from a specific cell. As soon as the get number in the cell changes, the forum is also adjusted. Is this possible? a kind of dynamic link

=XLOOKUP(A4;HIKER_VERDELING_2023!G$27:Z$27;HIKER_VERDELING_2023!G$4:Z$4)

Welcome to the Spreadsheet.com community @Jelmer_Bazuin !

You can consider using INDIRECT formula in such a scenario as you need the cell-reference/cell-range to be dynamically decided.

Eg. =XLOOKUP(A4,INDIRECT(β€œHIKER_VERDELING_2023!G”&A1&β€œ:Z$”&A1),HIKER_VERDELING_2023!G$4:Z$4)

Here A1 cell value will decide the actual range. Also, when A1 cell value changes XLOOKUP will re-evaluate.

Thanks a lot! Is this also possible withe a importrange formula? Example: =IMPORTRANGE(INDIRECT"A3")

In cell A3 I will copy the spreadsheet link

Hartelijk bedankt! Is dit ook mogelijk met een importrange formule? Voorbeeld: =IMPORTRANGE(INDIRECT"A3")

In cel A3 kopieer ik de spreadsheetlink