Copy a relative reference formula from one sheet to another.

I am trying to copy a formula with a relative reference in one worksheet to
another worksheet but the relative reference does not change as it should.

For example, I have '1' in cell G1 on sheet A.
On sheet B, in cell G1, I have a formula "='A'!G1+1".
This should give me 2 in cell G1 on sheet B.
I tried to copy this formula to my other sheets which in theory should give
me 3, 4, 5, etc. However, instead of the formula adjusting to the previous
sheet, the formulas copied all refer to sheet A and I get 2 every time.

I have over 100 sheets I need this for and don't want to have to edit every
formula. Is there a way to copy relative reference formulas to different
sheets and have the reference adjust as it does when it is on the same sheet?

Thanks.

To make sure I understand, let me rephrase: you have a workbook with a
number of tabs in it, labeled A, B, C etc. A!G1 contains the value 1.
B!G1 contains the formula =A!G1+1 which you want to copy into the same
cell on C, D, and E. The formula should pick up the value from
previous tab and increment it by 1 each time, so that Z!G1 should show
26. Do I have that right?

I'm not aware of an automated function within Excel to do this. It can
be accomplished with a bit of code that for each tab reads the current
tab name, converts it to an ascii value, augments that value by one,
with exceptions for flipping from Z back to A and with accommodations
for tab names that are longer than 1 character, then using the new
ascii value to generate a formula in G1.

You can use a user-defined function. In a module, enter:
Function Prevsheet(ref As Range)
Set sh = Sheets(Range(Application.Caller.Address).Parent.Index - 1)
Prevsheet = sh.Range(ref.Address)
End Function
In worksheet (except first):
If Sheet1!A1 has 1, then in Sheet2!A1, enter:
=Prevsheet(A1)+1
and you'll see 2.
In Sheet3!A1, enter =Prevsheet(A1)+1 and you'll see 3
etc.
HTH

"Dave O" wrote:

> To make sure I understand, let me rephrase: you have a workbook with a
> number of tabs in it, labeled A, B, C etc. A!G1 contains the value 1.
> B!G1 contains the formula =A!G1+1 which you want to copy into the same
> cell on C, D, and E. The formula should pick up the value from
> previous tab and increment it by 1 each time, so that Z!G1 should show
> 26. Do I have that right?
>
> I'm not aware of an automated function within Excel to do this. It can
> be accomplished with a bit of code that for each tab reads the current
> tab name, converts it to an ascii value, augments that value by one,
> with exceptions for flipping from Z back to A and with accommodations
> for tab names that are longer than 1 character, then using the new
> ascii value to generate a formula in G1.
>
>

This worked and will save me a lot of time. THANK YOU SO MUCH!!

"Bob Umlas, Excel MVP" wrote:

> You can use a user-defined function. In a module, enter:
> Function Prevsheet(ref As Range)
> Set sh = Sheets(Range(Application.Caller.Address).Parent.Index - 1)
> Prevsheet = sh.Range(ref.Address)
> End Function
> In worksheet (except first):
> If Sheet1!A1 has 1, then in Sheet2!A1, enter:
> =Prevsheet(A1)+1
> and you'll see 2.
> In Sheet3!A1, enter =Prevsheet(A1)+1 and you'll see 3
> etc.
> HTH
>
> "Dave O" wrote:
>
> > To make sure I understand, let me rephrase: you have a workbook with a
> > number of tabs in it, labeled A, B, C etc. A!G1 contains the value 1.
> > B!G1 contains the formula =A!G1+1 which you want to copy into the same
> > cell on C, D, and E. The formula should pick up the value from
> > previous tab and increment it by 1 each time, so that Z!G1 should show
> > 26. Do I have that right?
> >
> > I'm not aware of an automated function within Excel to do this. It can
> > be accomplished with a bit of code that for each tab reads the current
> > tab name, converts it to an ascii value, augments that value by one,
> > with exceptions for flipping from Z back to A and with accommodations
> > for tab names that are longer than 1 character, then using the new
> > ascii value to generate a formula in G1.
> >
> >