Consolidation of data from cell in active sheet of closed workbook

[Guest]

Your expert advice would be most appreciated:
Using Excel 2003, I want to sum the contents of cell A1 on the last active
worksheet of 22 other workbooks into cell A1 of a master consolidation
workbook. The active sheet in the 22 workbooks will change from week to
week, so I need the master workbook to only retrieve data from cell A1 in the
sheet that was visible when the other 22 workbooks were last saved.

I can put a formula in the master consolidatioin workbook to link to a named
worksheet in the 22 other workbooks but cannot get it to link to the cell in
the last active worksheet.

I hope this makes sense, and that you can assist.

Regards

 

[Guest]

Neil you can't have volatile links that link to the active sheet.

The easiest way to do it is to have a consistent naming convention and then
do a shearch replace to replace last week's links with this weeks.

Or write some vba to do it using a table.

There is the indirect function but this only works if the worksheets are
open and therefore not very good.

 

[Guest]

Martin,

Thanks for your prompt response.

Having searched for a solution in Excel's help files, to no avail, i thought
VBA might be the answer. I can perform basic macro editing, but I think this
task will be beyond my capabilities.

Reluctantly, I will continue to perform a search & replace on the cell
formulas, as you have suggested, unless you can point me in the direction of
a helpful online resource.

Many thanks again.

Neil

 

[Guest]

Neil

The easiest way to do it is to record a macro to replace the text.

You need to set up a cell where you can remeber the current string so that
you can use a message box to ask the user the type in the replacement text.

Get your code working and I'll have a look at it.