How do I reference a cell to another in the preceding worksheet

K

karl.sonja

When I create another worksheet (ie copy a sheet) I want to refer in the new
worksheet to some cell values in the PRECEDING worksheet. How do I refer to
that sheet in the formulae so that it always refers to the previous sheet.
 
S

Stephen Lloyd

If you wanted to reference A1 in Sheet3 of the same workbook:
=Sheet3!A1

If you wanted to reference A1 in Sheet3 of Book3.xls:
=[Book3.xls]Sheet3!A1

I hope that helps
 
J

J Sedoff comRemove>

In front of each cell reference add the sheet name. So for the following:
=A1/A3
will change to:
='New SheetName'!A1/'New Sheetname'!A3

Hope that helps, Jim
 
S

Stephen Lloyd

Jim's post makes a great point. If the name of your sheet has spaces you
need to enclose it with quotes:

=ThisSheet!A3
vs
='This Sheet'!A3
 
R

RagDyer

What is the naming routine of your sheets.

It's much easier to automate something like this if they're numbered in some
fashion.
 
K

karl.sonja

I will give you my example of a cell ='SV 122'!N43
My sheets are named SV 120, SV 121, SV 122 etc
When I create sheet SV 122 by copying SV 121 I have to manually edit ='SV
121'!N43 to ='SV 122'!N43.
This is what I want to avoid doing because I have a large number of cells to
edit each time I create the next sheet.
Is there a way I can increment automatically the 121 to 122 during the
copy/create a new sheet process?
 
G

Glenn

karl.sonja wrote:
I will give you my example of a cell ='SV 122'!N43
My sheets are named SV 120, SV 121, SV 122 etc
When I create sheet SV 122 by copying SV 121 I have to manually edit ='SV
121'!N43 to ='SV 122'!N43.
This is what I want to avoid doing because I have a large number of cells to
edit each time I create the next sheet.
Is there a way I can increment automatically the 121 to 122 during the
copy/create a new sheet process?



If you want to replace all references to

'SV 121'

with

'SV 122'

then select the columns, rows or the whole sheet (as needed) and use "Find and
Replace".
 
G

Gord Dibben

Karl

If you're willing to use a User Defined Function which ignores sheet names.

Function PrevSheet(rg As Range)
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

=PrevSheet(F14)

=SUM(A1:A10) + Prevsheet(F14)


Gord Dibben MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top