Relative worksheet reference clarified

[Debbie]

I believe my question needed a little clarification. I have a workbook with
several worksheet pages. I am creating a summary worksheet page within the
multiple worksheet workbook. Here is what I have:
=+'Sheet1'!$H$52
I need to be able to copy and paste that for several rows on the summary
worksheet page AND have it automatically change the SHEET reference to the
next sheet.
Example:
=+'Sheet2'!$H$52
=+'Sheet3'!$H$52
=+'Sheet4'!$H$52
SO the worksheet page is relative and the cell reference it absolute.
Now, when I copy and paste it keeps the sheet reference the exact same and
doesn't change the sheet reference to the next # automatically.

 

[Chip Pearson]

The only way to do this without using VBA code is to have the sheet
named such that they can be referenced numerically. For example, if
you leave the names as the defaults Sheet1, Sheet2, ...., you can
have a formula like

=INDIRECT("Sheet"&ROW()&"!$H$52")
this will increment the sheet name as you copy down a column because
the ROW() value will be different. If your formulas don't start on row
1, you'd need to subtract the starting row from ROW() and add 1 to get
the sheet names. EEG.,

=INDIRECT("Sheet"&ROW()-5+1&"!$H$52")

if your formulas start on row 5.

Sans VBA, Excel has no mechanism for relative sheet reference (e.g.,
you can't do something like =PreviousSheet!$H$52) nor does it have a
mechanism for accessing a sheet via its position. E.g., nothing like
=Sheet(2)!$A$1. That's too bad because it would be a very useful
feature.

All of this, though, can be done with some relatively simple VBA code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JLatham]

A little Indirect trickery and things should work for you.

First, a side comment: the + in =+ is not required. That's a hold-over
from Lotus days. ='Sheet2'!$H$52 would achieve the same thing, with a little
less typing.

OK. Assuming your first formula goes into a cell on row 2, this would
return H52 from Sheet1:
=INDIRECT("'Sheet" & ROW()-1 & "'!H52")
and as you fill the formula down the sheet, it will increment the number
associated with sheet. The key is in the ROW()-1 part of the formula.
Remember I said this formula was for row 2. ROW() returns the number of the
row the formula is in, so when in row 2 it returns 2, and 2-1 = 1, so the
reference becomes, effectively,
=INDIRECT("'Sheet1'!H52")

If you entered the first formula at row 3, then to get Sheet1 out of it the
formula would have become
=INDIRECT("'Sheet" & ROW()-2 & "'!H52") ' 3-2=1

Since the indirect reference is actually text, there's no requirement for
the absolute addressing; the H52 will remain H52 for all entries. But you
can change it to:
=INDIRECT("'Sheet" & ROW()-1 & "'!$H$52")
for clarity if you like.

Hope this helps.

 

[Dave Peterson]

And you have another response at your other post.

I believe my question needed a little clarification. I have a workbook with
several worksheet pages. I am creating a summary worksheet page within the
multiple worksheet workbook. Here is what I have:
=+'Sheet1'!$H$52
I need to be able to copy and paste that for several rows on the summary
worksheet page AND have it automatically change the SHEET reference to the
next sheet.
Example:
=+'Sheet2'!$H$52
=+'Sheet3'!$H$52
=+'Sheet4'!$H$52
SO the worksheet page is relative and the cell reference it absolute.
Now, when I copy and paste it keeps the sheet reference the exact same and
doesn't change the sheet reference to the next # automatically.

 

[Chip Pearson]

Another way to do it would be to create a VBA function as:


Function SheetNames() As Variant
Dim Arr() As String
Dim N As Long
Dim WB As Workbook
ReDim Arr(1 To Application.Caller.Rows.Count)
Set WB = Application.Caller.Worksheet.Parent
With WB.Worksheets
For N = 1 To .Count
Arr(N) = .Item(N).Name
Next N
End With
SheetNames = Application.Transpose(Arr)
End Function


Then, select some vertical range, say F10:F20, type

=SheetNames()

and press CTRL SHIFT ENTER.
Since this is an Array Formula, you *must* press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula
and whenever you edit it later. If you do this properly,
Excel will display the formula in the Formula Bar enclosed
in curly braces { }. (You do not type the curly braces -
Excel includes them automatically.) The formula will
not work properly if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/excel/ArrayFormulas.aspx for lots
more information about array formulas.

Then, you can use a formula like

=INDIRECT("'"&F10&"'!$H$52")

and fill down. The reference to F10 will increment as you fill down,
and thus return Sheet1, Sheet2, and so on. The names of the worksheet
need not have any name pattern or number. Any name will work.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)