Auto linking to other worksheets

i have a worksheet which has the months of the year across the top i.e. May-20, June-20 etc

I then have rows beneath each which pull in data from worksheets for those months using the filename as a reference - PNMS Billing - May 2020.xlsx....etc

How can i do this so that I can just drag across from May and the month part of the reference is updated to Jun 2020.xlsx etc, rather than having to configure them all individually.

See attached for details

You would need o use something like:

Formula:

Please Login or Register  to view this content.

You may need to adjust the Text element depending on how longer names appear in the file names

Ok so when i use that, customised as suggested, I get a ref error, however when open the monthly file up it updates the form?

Remove the Indirect function which will cause it to output just the completed string... check that against the value in the cell that is working and double check for any missing spaces/characters etc

Hi

I've stripped this back as all the files are in the same directory, so now have

=INDIRECT("'[PNMS Billing - " & TEXT(V$1,"mmm yyyy") & ".xlsx]Invoice - HJA'!$F$18")

Taking the INDIRECT out, as you advised to check, gives me

'[PNMS Billing - Jun 2020.xlsx]Invoice - HJA'!$F$18

Which looks right?