Copying and using formulas including worksheet names

[Isissoft]

I have workbooks that contain sheets names with consecutive month names -
like Jan07, Feb07 etc.

I have a cell containing running totals from previous sheets plus some
figures from the current sheet.

I want to copy and paste the formula onto a new sheet when I create one but
I want the sheet names to be updated to reflect the fact that I am using a
new sheet. So the old formula might be;

='Jan-07'!E21+'Feb-07'!C18

and I want the new formula to be

='Feb-07'!E21+'Mar-07'!C18

Is it possible to do this using Excel 2002 (from Office XP)

Would it also be possible for me to get the new sheet added automatically
ready filled in and named correctly using a Macro or Visual Basic ?

Any help much appreciated.

Thanks

 

[loudfish]

I have workbooks that contain sheets names with consecutive month names -
like Jan07, Feb07 etc.

I have a cell containing running totals from previous sheets plus some
figures from the current sheet.

I want to copy and paste the formula onto a new sheet when I create one but
I want the sheet names to be updated to reflect the fact that I am using a
new sheet. So the old formula might be;

='Jan-07'!E21+'Feb-07'!C18

and I want the new formula to be

='Feb-07'!E21+'Mar-07'!C18

Is it possible to do this using Excel 2002 (from Office XP)

Would it also be possible for me to get the new sheet added automatically
ready filled in and named correctly using a Macro or Visual Basic ?

Any help much appreciated.

Thanks

2 options.

1. Rebuild all the formulas using INDIRECT(ADDRESS()) so that you can
specify the names of both the source worksheets as variables on the
destination worksheet. That way, you just need to change these two
variables. For your specify example:

A1: Feb-07
A2: Mar-07
A3: =INDIRECT(ADDRESS(21,5,1,1,A1))+INDIRECT(ADDRESS(18,3,1,1,A2))

should give the same result as:
='Feb-07'!E21+'Mar-07'!C18

But ensure A1 and A2 are entered as text - put a ' in front, otherwise
Excel will try to convert them into dates.

2. Copy the working destination worksheet (ie last months).
Rename it.
Search and replace 'Feb-07'! with 'Mar-07'! - replace all.
(leaving the ' and ! in, means it should only pickup the formulas)
Search and replace 'Jan-07'! with 'Feb-07'! - replace all.
(note the specific order, otherwise everything ends up as 'Feb-07'!)

Option 1 would take a while to rebuild everything.
Option 2 is faster, and unless it takes ages to run the find and
replace, is the much simpler solution.

HTH

Andrew

 

[Isissoft]

2 options.

1. Rebuild all the formulas using INDIRECT(ADDRESS()) so that you can
specify the names of both the source worksheets as variables on the
destination worksheet. That way, you just need to change these two
variables. For your specify example:

A1: Feb-07
A2: Mar-07
A3: =INDIRECT(ADDRESS(21,5,1,1,A1))+INDIRECT(ADDRESS(18,3,1,1,A2))

should give the same result as:
='Feb-07'!E21+'Mar-07'!C18

But ensure A1 and A2 are entered as text - put a ' in front, otherwise
Excel will try to convert them into dates.

2. Copy the working destination worksheet (ie last months).
Rename it.
Search and replace 'Feb-07'! with 'Mar-07'! - replace all.
(leaving the ' and ! in, means it should only pickup the formulas)
Search and replace 'Jan-07'! with 'Feb-07'! - replace all.
(note the specific order, otherwise everything ends up as 'Feb-07'!)

Option 1 would take a while to rebuild everything.
Option 2 is faster, and unless it takes ages to run the find and
replace, is the much simpler solution.

HTH

Andrew

Andrew,

Only just got yor answer - thanks very much that is making sense to me.
Can I do this in VB so that I can write a routine ?

Thabnks for your help - sorry for the delay.

 

[loudfish]

Only just got yor answer - thanks very much that is making sense to me.

Can I do this in VB so that I can write a routine ?

Thabnks for your help - sorry for the delay.

Sorry for delay in response.
Option 1 shouldn't require VBA.
Option 2 could easily be coded, but I guess you are changing something
once a month in loads of places - it's the same search and replace
code run multiple times, so you just keep hitting "replace all". If
you want to turn it into code, just record it as you do it the first
time, then you should be able to extract the code out - it'll be
something like.

Selection.Replace What:="bob", Replacement:="tim", LookAt:=xlPart,
_
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False

(where "bob" and "tim" are the "before" and "after" months.... the
names of your worksheets)

HTH

Andrew