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

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