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

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.


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Neil X Peel" wrote:

> 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

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



"Martin Fishlock" wrote:

> 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.
>
>
> --
> Hope this helps
> Martin Fishlock, Bangkok, Thailand
> Please do not forget to rate this reply.
>
>
> "Neil X Peel" wrote:
>
> > 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

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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Neil X Peel" wrote:

> 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
>
>
>
> "Martin Fishlock" wrote:
>
> > 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.
> >
> >
> > --
> > Hope this helps
> > Martin Fishlock, Bangkok, Thailand
> > Please do not forget to rate this reply.
> >
> >
> > "Neil X Peel" wrote:
> >
> > > 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