how do I paste a relative formula in multiple sheets?

V

vrietz

I have multiple sheets in a workbook. One sheet for each business day of the
month. I want to be able to carry over one cell (F66) to the next day's
sheet's (F3). THat in itself is easy enough, however the problem comes when
I try to copy and paste that formula ( ='Day 2'!F66) to the next day's sheet.
The reference to 'Day 2' stays absolute. I need it to be relative. As I
paste the formula, I need it to change the 'Day 2' to 'Day 3' and so on. It
needs to just pick up the name of the sheet prior to itself. I know there
must be a way to do this, but I cant figure it out. Help!
 
H

Harlan Grove

vrietz said:
I have multiple sheets in a workbook. One sheet for each business day
of the month. I want to be able to carry over one cell (F66) to the
next day's sheet's (F3). THat in itself is easy enough, however the
problem comes when I try to copy and paste that formula ( ='Day 2'!F66)
to the next day's sheet. The reference to 'Day 2' stays absolute. I
need it to be relative. As I paste the formula, I need it to change
the 'Day 2' to 'Day 3' and so on. It needs to just pick up the name of
the sheet prior to itself. I know there must be a way to do this, but
I cant figure it out. Help!
Click to expand...

Excel really isn't a 3D spreadsheet. Just a 2D spreadsheet with some 3D
consolidation features. What you want to do can be done, but it requires
either an ordered list of worksheet names or VBA. See

http://www.google.com/[email protected]
 
R

RagDyer

There's problem with that procedure Bob, and shouldn't be used!

Cells named with that method will *not* auto-calculate after the initial
value entry.

--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


message define a name, like abc, but in the refers to box, define it as:
!F66
(Note the leading exclamation point). This will define it as globally local
to the active sheet. Then use the name abc (or whatever name you chose) and
it will refer to the local sheet.
Bob Umlas
Excel MVP
 
R

RagDyer

This may be too little, too late.

You can follow Bob's idea, and name your F66 cell to be worksheet specific,
but don't use the exclamation procedure.

Then, any sheet *copied* from this sheet will *automatically* contain that
cell name, and be WS specific to the new sheet.

You could then base your formulas on the name of that particular cell.

Like I said, maybe too late if all your sheets are already created and
populated with data.
Something to remember for the next time, though.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I have multiple sheets in a workbook. One sheet for each business day of
the
month. I want to be able to carry over one cell (F66) to the next day's
sheet's (F3). THat in itself is easy enough, however the problem comes when
I try to copy and paste that formula ( ='Day 2'!F66) to the next day's
sheet.
The reference to 'Day 2' stays absolute. I need it to be relative. As I
paste the formula, I need it to change the 'Day 2' to 'Day 3' and so on. It
needs to just pick up the name of the sheet prior to itself. I know there
must be a way to do this, but I cant figure it out. Help!
 
G

Gord Dibben

If you're willing to use a User Defined Function.........

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
Application.Volatile
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Copy/paste to a general module in your workbook.

Select second sheet then SHIFT + Click on last sheet to group them.

In a cell enter =PrevSheet(F66)

Ungroup the sheets.

Gord Dibben Excel MVP
 
Top