Method for creating formulas to reference different worksheets?

What is the method for creating an Excel formula that references information
on the first worksheet from a second worksheet?

Hi,

This is a very simple one

=Sheet1!A1

Mike

"copticdred" wrote:

What is the method for creating an Excel formula that references information
on the first worksheet from a second worksheet?

=Sheet2!A1 to reference Sheet2 A1

--
If this post helps click Yes
---------------
Jacob Skaria


"copticdred" wrote:

What is the method for creating an Excel formula that references information
on the first worksheet from a second worksheet?

If you want a cascading type of reference that always references the sheet
previous............................

If you're willing to use a User Defined Function this becomes quite
easy.......

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

Example of use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP

On Thu, 16 Apr 2009 09:49:01 -0700, copticdred
wrote:

What is the method for creating an Excel formula that references information
on the first worksheet from a second worksheet?