dynamically reference a sheet name within closed workbook and return value

[greenkevin86]

I have a workbook named "Results" with a row containing the weeks in a year 1 through 52. There is another closed workbook named "Daily Stats" that I would like to dynamically reference a particular sheet name based on the week (each sheet is named W1, W2, W3, etc) and return a value from a common cell ($O$3), and use this value within a calculation. The closed workbook has individual sheets for each week, and the value I would like to return will be variable based on what week I am on (goal of copying this formula across all 52 weeks so I don't have to manually open the workbook and get the value).

The following formula works but ONLY IF I have the "Daily Stats" file open:

=INDIRECT("'F:\Shared\Daily Management Review\[2019 Daily Stats.xlsx]W"&C22&"'!$O$3")/1000


Is there a way to get this to work without opening the workbook (when closed, I get a #REF error on each of the cells this formula is copied to)?

 

[James006]

Hi,

John Walkenback has an excellent trick on how to Extract a Cell Value from a Closed Workbook ...WITHOUT Opening it ...

http://spreadsheetpage.com/index.php...a_closed_file/

Hope this will help

 

[greenkevin86]

Hi,

John Walkenback has an excellent trick on how to Extract a Cell Value from a Closed Workbook ...WITHOUT Opening it ...

http://spreadsheetpage.com/index.php...a_closed_file/

Hope this will help

Link isn't working for me.

 

[James006]

You are welcome ...

Don't ask me why the first time ... the link did not copy properly ...

http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/


HTH