Hi,

This is a tricky one, but I'm hoping somebody can help me.

I have a file with a series of sheets based on date. There is one sheet
for each week. Any one sheet has many references to the sheet before it.
E.g in the sheet called Jan8, there are many references to Jan1, as in
"=Jan1!D33", "=Jan1!H33", etc.

Each week I add a new weekly sheet, copies from the previous sheet, but
then I have to manually update about twenty references. The actual cell
reference remains the same, but the sheet name changes.

Is there any way I could automate this. It would be really nifty if it
could be done from the sheet name, but even if I had to change the
reference once, that would be OK !!

Any ideas would be greatly appreciated,

Thanks,
Paul
(as you may have guessed, the email address is a furphy)
(a real address is -thegerm at netspace dot net dot au-)

Use the address function

Put in cell A1 = Jan1

=ADDRESS(33,4,1,FALSE,A1)

"JOhn Smith" wrote:

Hi,

This is a tricky one, but I'm hoping somebody can help me.

I have a file with a series of sheets based on date. There is one sheet
for each week. Any one sheet has many references to the sheet before it.
E.g in the sheet called Jan8, there are many references to Jan1, as in
"=Jan1!D33", "=Jan1!H33", etc.

Each week I add a new weekly sheet, copies from the previous sheet, but
then I have to manually update about twenty references. The actual cell
reference remains the same, but the sheet name changes.

Is there any way I could automate this. It would be really nifty if it
could be done from the sheet name, but even if I had to change the
reference once, that would be OK !!

Any ideas would be greatly appreciated,

Thanks,
Paul
(as you may have guessed, the email address is a furphy)
(a real address is -thegerm at netspace dot net dot au-)

Hi Joel,

Thanks for your response, however its not quite what I wanted, although
I think it must be close.

Using your example the cell with the ADDRESS formula returns (no quotes)
"Jan1!R33C4". This is good, except that Excel doesn't see this as a
formula. I tried adding an '=' to the front with CONCATENATE but still
no luck. I suspect Excel is still seeing this as a string literal, not a
formula.

So, then I remembered seeing the INDIRECT function and if I apply that
to the cell where the ADDRESS function is, lo and behold it works!!!
exactly what I wanted.

Thanks again for your help!!
...Paul


Joel wrote:
Use the address function

Put in cell A1 = Jan1

=ADDRESS(33,4,1,FALSE,A1)

"JOhn Smith" wrote:

Hi,

This is a tricky one, but I'm hoping somebody can help me.

I have a file with a series of sheets based on date. There is one sheet
for each week. Any one sheet has many references to the sheet before it.
E.g in the sheet called Jan8, there are many references to Jan1, as in
"=Jan1!D33", "=Jan1!H33", etc.

Each week I add a new weekly sheet, copies from the previous sheet, but
then I have to manually update about twenty references. The actual cell
reference remains the same, but the sheet name changes.

Is there any way I could automate this. It would be really nifty if it
could be done from the sheet name, but even if I had to change the
reference once, that would be OK !!

Any ideas would be greatly appreciated,

Thanks,
Paul
(as you may have guessed, the email address is a furphy)
(a real address is -thegerm at netspace dot net dot au-)

Hi Paul,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JOhn Smith" wrote in message
.au...
Hi,

This is a tricky one, but I'm hoping somebody can help me.

I have a file with a series of sheets based on date. There is one sheet
for each week. Any one sheet has many references to the sheet before it.
E.g in the sheet called Jan8, there are many references to Jan1, as in
"=Jan1!D33", "=Jan1!H33", etc.

Each week I add a new weekly sheet, copies from the previous sheet, but
then I have to manually update about twenty references. The actual cell
reference remains the same, but the sheet name changes.

Is there any way I could automate this. It would be really nifty if it
could be done from the sheet name, but even if I had to change the
reference once, that would be OK !!

Any ideas would be greatly appreciated,

Thanks,
Paul
(as you may have guessed, the email address is a furphy)
(a real address is -thegerm at netspace dot net dot au-)