Indirect.Ext

[lisach]

Thanks to a great post I found here, I installed the Morefunc Add-In. I can now pull the value from a specific cell in a closed workbook. YEAH! But... I need more help. I have a master sheet where in Column A I have 52 different workbooks listed. Each workbook is the same just for a different week. Week Ending 02-10-2007.xls ... Week Ending 02-17-2007.xls ... Week Ending 02-24-2007.xls ... etc.... The adjacent cells in the next columns need to pull the value from that workbook from the specific sheet "weekend" cell B24 C24 B25 C25

Using the INDIRECT.EXT function I can do that. But I don't want to type out the formula in each cell referencing the 52 different workbooks. I would prefer to just look at column A and pull the name from there.

I have attached the sheet for your consideration.
thanks in advance.

2007 Summary.xlsx

[Pete_UK]

Try it this way in B4:

=INDIRECT.EXT("'["&$A4&"]Weekend'!B24")

so that it picks up the filename from cell A4. You will need to have similar formulae in C4:E4, but with the B24 at the end changed as appropriate, then you can copy the formulae down.

Hope this helps.

Pete

[lisach]

thanks Pete, Looks go so far except.... what do I need to do to reactivate the formula after copying it down?

[lisach]

oops...No... ["&$A4&"] doesn't work when the sheets are closed. Works if they are open though!

[Pete_UK]

This is the formula that was in B4 in the sheet you posted:

=INDIRECT.EXT("'[Week Ending 02-03-2007.xls]Weekend'!B24")

This implies that the workbook is open at the same time as you did not have the full path in front of the filename. If the workbook is closed then you need to include the full path to the file so that Excel knows where to get it from. You can put the path in a cell somewhere, eg H1 (including the final "\"), and then my formula would become:

=INDIRECT.EXT("'"&$H$1&"["&$A4&"]Weekend'!B24")

If the path does not include the final "\", then you can do this:

=INDIRECT.EXT("'"&$H$1&"\["&$A4&"]Weekend'!B24")

A simple way of determining the path is to have two workbooks open, and in one of them begin to enter a formula by typing =. Then click to the window of the other file and select a cell then press enter. Then you can close the second workbook and you will see the full path and filename to the cell that you had selected.

Hope this helps.

Pete

[lisach]

thank-you so much for your kind help here. I had studied the MOREFUNC help guide on the topic of INDIRECT.EXT and according to it, "The Reference argument can be one of the following :...... If the workbook is in the same directory : "'[WorkbookName.xls]SheetName'!A1" The 2007 Summary workbook is located in the same directory as the 2007 week ending workbooks so this was the syntax I was following.

VERY INTERESTING DISCOVERY
However, now following your advice and using the address in cell H1. The cells were still showing invalid cell reference except for 2 which updated and displayed the values. Opening the workbooks would update the values. Closing it... invalid cell reference again. funny thing was just opening the file and doing nothing, closing requested to save changes. I saved the changes and suddenly the values were good. Then I remembered all these files had been downloaded from Google Docs and converted to excel. They had never been opened since then. After opening them all and saving changes the spreadsheet updated correctly all though.

Thanks for you help. Still curious about the help file saying you don't have to include the path if the file is in the same directory. Maybe I misinterpreted it.

thanks again.

[Pete_UK]

I would always advise using the full path, as it takes away any uncertainties, but that's an interesting discovery you made there, Lisa - thanks for feeding back.

Pete

[lisach]

yes it is isn't it? Very strange. But I guess could be expected since google docs is obviously not our beloved EXCEL!