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.
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.
Is there a way to do it without changing the name? I'm afraid that it will get overlooked and forgotten if we can't figure out a way to make it work.
Hi Julie,
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
='d:temp[book1.xlsx]Sheet1'!A1
More info here
Regards
Phil
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.
Sunny
