Link in Formula

[CJS548]

Hi,
I'm using index match reference, but I have to change every formula one by one, is there a faster way?

https://ibb.co/X2pcKzP

See the photo attached, I have to change the formula in G56 from '[7 July Shop Yields.xlsx]1.7' to '[7 July Shop Yields.xlsx]2.7'! manually but whereby F56 already stated 2.7, is there a way where it can take reference from there?
Thanks in advance!!

 

[Fluff]

Have a look at the Indirect function https://exceljet.net/excel-functions/excel-indirect-function

 

[Special-K99]

If you also incorporate INDEX(A$55:A$1000,ROW($1:1),1) you can pick out the 1.7, 2.7 etc going down the column
So something like

INDIRECT("'[7 July Shop yields.xlsx]"&INDEX(A$55:A$1000,ROWS($1:1),1)&"'!$AA$200:AA339")

and adjust the second part of the formula too

 

[Fluff]

Or even
INDIRECT("'[7 July Shop Yields.xlsx]"&F56&"'!"$AA$200:$AA339")

 

[CJS548]

Thank you all for the information!
It did work when both the spreadsheet is opened, but once the reference is turned off, the 'link' kinda changed, but nevermind, I will slowly digest the indirect function, I'm starting to get a hang of it now...
thanks guys, you are awesome!

 

[Fluff]

Indirect only works on open workbooks and when the "7 July Shop Yields.xlsx" workbook is closed you will see the full file path in the formula