Same table, different and dynamic workbook names

[BecSkull]

Hello,

Long time lurker, first time poster (I think!)

I'm looking for a no code option, in the first instance. I have PA in my arsenal but am hoping not to have a solution outside Excel. My company frowns upon vba. It's not banned but there has to be a good reason & my 20 minute job wouldn't qualify.

On a Monday, there is a report that is run with the current date and last week number in the format of 'ddmmyy' report - Wk 37. (Don't even ask with that formatting!)

I need to copy a table from that report into my spreadsheet, basically. Each week, the same cells are copy and then pasted in the same area. I've tried to set up a dynamic formula which works but I can't get it to hyperlink.

="='K:\path\goes\here\SLT KPI Reports\[" & G3 & " - SLT KPI - Wk " & E3 & ".xlsx]Overview'!$L$3"

Cell G3 is hard coded with the date, i couldn't get the microsoft date format right to work properly with =Today()-4 (I run the report on a Friday)

Cell E3 is the week number - isoweeknum(cell)-1

I feel like I'm missing something really easy - any help would be appreciated

[#2]

not sure what you mean by 'hyperlink' but I think you are looking for INDIRECT() which will take a text representation of a reference and apply it as a reference but that said I don't think that will work for unopened workbooks and such. I would go with Power Query and in the power query create a dynamic filename reference and just refresh that power query
as for G3 maybe try =TEXT(TODAY()-4,"DDMMYY")