November 8, 2018
I have a workbook that has a macro and each week generates a new sheet for us to work in. I would like to put a formula in O119 of the attached workbook, to automatically look up a number in a different workbook. I can't attach the other workbook because there is too much personal info.
In the other workbook, named TRIP-Rebates20181030, (the name changes every 6 months. ex TRIP-Rebates20180329, TRIP-Rebates20171030, etc) there are several sheets but on the Family Balance Sheet in cell D3 there is a number that I want to grab every time we update the spreadsheet.
Is there a way to do this since the name changes I'm having a hard time figuring out a formula to grab it.
July 26, 2013
Would it be possible to manually enter the name of the current other file when the new sheet is created?
If so and let's say the file name is put in Cell A3, the following formula in O119 should work:
=INDIRECT("'["&A3&".xlsm]Family Balance Sheet'!D3")
I have not looked at the macro but, assuming it duplicates the previous week as a starting point, then you would only need to change the input name once every 6 months.
November 8, 2018
October 5, 2010
If you change the name of the workbook you want to get data from, then you need to change that same name in the formula you want to create in O119. Or am I missing something?
As Paul illustrates, you can create a reference to the external file like so
More info here
June 25, 2016
If I am not mistaken, INDIRECT does not work with a closed workbook.
The source workbook must be open for it to calculate and the INDIRECT formula must include the full path where the source workbook is located.
So, if you need to open the source workbook, you might just as well copy the value straight from the source.