Hi all
I've built a nice looking "Front End" worksheet that should hopefully read from weekly reports downloaded to a shared folder.
As there's a report for each week I wanted to have a drop down list which let you select which week you wanted to look at, which would then be used as part of an INDIRECT function to show the relevant data. I've managed to do this, but it requires all the workbooks you're referring to to be open, which is cumbersome.
Currently
Cell B1 contains the Drop Down Menu with Week Number
=INDIRECT("'[BOL Week " & $B$1 & ".xls]Sheet 1'!A" &ROW(),TRUE) - this works to refer to the correct cells in the open Workbook.
=INDEX('C:\Users\FlashGordy\Shared Folder\[BOL WEEK 16.xls]Sheet 1'!A:A,ROW(),1) - this works to refer to the correct cells in a closed workbook but doesn't use the drop down week number to update the page for different weeks.
I thought
=INDEX(Indirect("'C:\Users\FlashGordy\Shared Folder\[BOL WEEK " & $B$1 ".xls]Sheet 1'!A:A,ROW(),1)"))
Would work but excel says it's not a valid formula.
I'm pretty stuck - any help would be appreciated.
Thanks
Bookmarks