external workbook data link with variable workbook reference

[dlorenc]

I have a number or workbooks containing weekly status reports..I would like to aggregate them into a single workbook for mgmt access...

Given:
all workbooks (status reports) are formatted identical;
all worksheets (a week status report is contained in a worksheet with the week in the format of "mm.dd.yy")

what is the syntax to make the worksheet reference variable?

this works:
='https://myportal.com/Construction Weekly Reports/[P06-WS.xlsx]Summary'!AC$9

if I put "Summary" into cell A1... what is the syntax? This does not work...

='https://myportal.com/Construction Weekly Reports/[P06-WS.xlsx](A1)'!AC$9

****

note - I dont want the exec to 'open' all vendors workbooks so that a 'indirect' could work...

my only thought is to go into the target workbooks as create a 'current week' worksheet so that the referenced worksheet would be consistent...

ie... ='https://myportal.com/Construction Weekly Reports/[P06-WS.xlsx]CurrentWeek'!AC$9

 

[dlorenc]

thinking a bit out of the excel box... I have no problem making the aggregate a word doc... but then I still have the problem of trying to point the aggregate report to a 'different' current week...each week?.

1. In a work doc, paste in a linked excel worksheet (current week)
2. NEXT week, redo the 'current' week.. (again, I COULD create a 'current week' worksheet in each status report...then I guess the current week info is changed in each status report workbook...?..)..