How do I link cells between to sheets in MS Excel?

  • Thread starter Thread starter manhut
  • Start date Start date
M

manhut

I have an Excel workbook pulled up with 3 different sheets. I need to link
cells between one sheet to the next. How do I do that?
 
Simple example in EXCEL 2007:-

1. In Sheet1 cell A1 type:-

this is cell a1

2. I Sheet2 cell A1 type:-

=Sheet1!A1

The contents of Sheet1 cell A1 will now be pulled into Sheet2 cell A1.

Please hit Yes if my comments have helped.

Thanks.
 
The easiest way is to get Excel to create the formula for you.
-- Type = into a cell
-- Navigate to the cell on the other worksheet; you will see in the formula
bar that Excel puts in the cell's address
-- Type any other operator (like + , etc.)
-- Navigate to another cell
-- Continue until the formula is completed, then hit Enter.

Once you learn this system, you will never type a cell address again.

Regards,
Fred
 
There is no "next sheet" or "previous sheet" function available. You
have to hard code the sheet reference in formula or use VBA. For a
formula on Sheet1, use

='Sheet2'!A1

to return A1 from Sheet2. Similarly, on sheet2, you would have

='Sheet3'!A1

to get A1 from Sheet3. The single apostrophes are required in the
formula only if the sheet name contains spaces, but are harmless if
the sheet name does not contain spaces.

You can create a function in VBA to get the next worksheet and then
use that in an INDIRECT call. EEG.,

=INDIRECT(NextSheet()&"!A1")

This will return the value in the cell A1 on the worksheet following
the worksheet on which the formula was entered.

The VBA code for NextSheet and its brother PrevSheet is shown below:

Function NextSheet(Optional R As Range, _
Optional Wrap As Boolean = False) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' NextSheet
' Return the name of the worksheet following the
' sheet contains R is R is not Nothing or following
' the sheet whence the function was called. If Wrap
' is omitted or False and the present sheet has no
' Next sheet, the result is vbNullString. If Wrap
' is True, and the present sheet is the last sheet,
' the code wraps around to the first sheet.
' The returned sheet name is enclosed in apostrophes.
'''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim WS As Worksheet
If R Is Nothing Then
Set WS = Application.Caller.Parent
Else
Set WS = R.Worksheet
End If
If Not WS.Next Is Nothing Then
NextSheet = "'" & WS.Next.Name & "'"
Else
If Wrap = False Then
NextSheet = vbNullString
Else
NextSheet = "'" & WS.Parent.Worksheets(1).Name & "'"
End If
End If
End Function

Function PrevSheet(Optional R As Range, _
Optional Wrap As Boolean = False) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' PrevSheet
' Return the name of the worksheet before the
' sheet contains R is R is not Nothing or before
' the sheet whence the function was called. If Wrap
' is omitted or False and the present sheet has no
' Previous sheet, the result is vbNullString. If Wrap
' is True, and the present sheet is the first sheet,
' the code wraps around to the last sheet.
' The returned sheet name is enclosed in apostrophes.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
If R Is Nothing Then
Set WS = Application.Caller.Parent
Else
Set WS = R.Worksheet
End If
If Not WS.Previous Is Nothing Then
PrevSheet = "'" & WS.Previous.Name & "'"
Else
If Wrap = False Then
PrevSheet = vbNullString
Else
With WS.Parent.Worksheets
PrevSheet = "'" & .Item(.Count).Name & "'"
End With
End If
End If
End Function


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Back
Top