No, the one before the one before

by Ross Mclean, 15/04/05
Accompanying workbooks 

Getting data from other sheets can be very easy: or it can be very hard, it all depends on what you need to do. This article looks at some of the ways you can get data from other sheets.

The basics
Using the Indirect function
Back to the old school, XLM! (previous and next sheet)
VBA user defined functions (previous and next sheet)
Links

The Basics

Ok, the simplest way to get data from sheets other than the one you in is to reference the sheet, then the cell:

=Sheet2!D24

Easy, the sheet name, followed by “!” the cell reference. The easiest way to do this is to put in the “=” sign in the cell you want the value to be displayed in, then click on the sheet/cell where you want the value to come from, excel does the formulas for you.

And when the worksheets are in different workbooks, you can use the same method, which produces:

=[Book1]Sheet2!$D$24

Ok, right off the bat there’s a few things to be aware of. By default excel makes references to external workbooks absolute. References can be relative, just remove the “$”s symbols, that’s fine.
The work book you reference needs to be open for the formula to work, and excel will ask if you want to open this workbook each time you open a calling workbook.

Tip!
And while we’re at it, I might as well point out that (in excel 2000+), under the Edit menu, towards the bottom, the “Link” menu item can be used to control your links – normally it’s used for removing “dead” links :-).

You can use this sort of referencing with most of excels standard functions, for example vlookup can be used in this way:

=VLOOKUP([Book3]Sheet1!$A$1,[Book2]Sheet1!$A$1:$B$11,2,0)

 

Using the Indirect function

The next trick we can employ is to use the indirect function. Now we can create a formula that refers to another sheet, but can be readily changed:

=INDIRECT($B$1&"D"&ROW())

In cell B1 we would have the name of the sheet and the “!” operator.

 indirect fuction example

Above you can see how all the formulas in column A refer to cell B1. The formula is simply re-creating the normal formula to address another worksheet, but instead of the worksheet name being “hard coded” it is gotten from a cell value. By changing the value of B1 all the values in the range A2:A6 will refer to a different sheet. You can see how, if your sheets are called for example, “Jan”, “Feb”, “March” etc. this could be really useful.
Notice the use of the “$” symbol. You can combine this to work with data validation, and also to work with other workbooks (example in the workbook that accompanies this article). If in cell B1 was a workbook name such as “[my_workbook]” and in cell B2 was the name of a sheet in that workbook “My sheet!” then we could use:

=INDIRECT($B$1&$B$2&"D"&ROW())

Notice that all workbook names must be enclosed in square brackets “[ and ]” and worksheets must be followed by a “!” mark. Also you will not get a message from excel asking if you want to open the workbook you are linking to.

Andrew Engwirda has some more ideas of how to work with indirect here:
http://blog.livedoor.jp/andrewe/tb.cgi/17053816

Indirect is a powerful function, but it has an Achilles heal – it will not work if your string (sheet name) has a space in it. For example “My_Sheet”, will behave as expected, but “My Sheet” will error out. If you are designing a work book from scratch than it’s not a problem, because you can use the “_” between words, if however you’re working on a existing books, then this UDF might save the day!

Public Function SheetName(byVal ws As String, byVal rng As Excel.Range)
SheetName = Worksheets(ws).Range(rng).Value
End Function


Back to the old school, XLM!

This method was suggested by Jan Karel Pieterse, of JKP Application Development on Dick Kusleika’s excel blog. Here’s the cut and trust:
 

Start Excel and open the workbook in which you want to start using this.
Now define these names (Insert, Name, Define):

AllSheets
=GET.WORKBOOK(1+0*now())
Gets an array of all sheets in the workbook

ThisSheet
=GET.CELL(32+0*now(),indirect(”rc”,False))
Gets the name of the sheet the name is used in.

PrevSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)-1+0*now())
Gives the name of the worksheet to the immediate left of the sheet where this name is used.

NextSheet
=INDEX(AllSheets,MATCH(ThisSheet,AllSheets,0)+1+0*now())
Gives the name of the worksheet to the immediate right of the sheet where this name is used.

The 0*Now() is added to ensure the names are “volatile” and get recalculated on every calculation of Excel.

To refer to cell B1 on the previous worksheet, use this formula:

=INDIRECT(”‘”&PrevSheet&”‘!”&CELL(”address”,B1))

Likewise for the next worksheet:
=INDIRECT(”‘”&NextSheet&”‘!”&CELL(”address”,B1))

To get the name of the previous sheet in a cell, use:
=PrevSheet

One serious warning is needed though.
Do not copy any cell that uses any of these defined names to another worksheet. Excel will crash!

The full article is here:
http://www.dicks-blog.com/archives/2004/12/02/referring-to-previous-sheet/


Very clever, but that crashing bit scares me! As Jan says (in the full article), most folks would use a UDF to achieve the same thing, which leads us to…
 

VBA user defined functions

Here are some possible VBA functions, the basic idea is to get the current pages index value (it’s position in the sheets collection) and refer to the sheet one before or after it. There is a little bit of error handling in case the sheet is not a worksheet (.type <> -4167), or if it’s the first or last sheet.

Function PrevSheet(Range As Range)
Application.Volatile
PrevSheet = Application.Caller.Parent.Index

If PrevSheet = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf Sheets(PrevSheet - 1).Type <> -4167 Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(PrevSheet - 1) _
.Range(Range.Address).Value
End If
End Function

can be used with as worksheet formula like:

=PrevSheet(A1)

and for the next sheet use:

Function NextSheet(Range As Range)
Application.Volatile
NextSheet = Application.Caller.Parent.Index

If NextSheet = Sheets.Count Then
NextSheet = CVErr(xlErrRef)
ElseIf Sheets(NextSheet + 1).Type <> -4167 Then
NextSheet = CVErr(xlErrNA)
Else
NextSheet = Sheets(NextSheet + 1) _
.Range(Range.Address).Value
End If
End Function

with

=NextSheet(A1)

These seem quite useful, but it might be just as easy, and more flexible, if we used some thing like this:

Function OffsetSheet(ByVal Offset As Integer, ByVal Range As Range)
Application.Volatile
OffsetSheet = Application.Caller.Parent.Index

If OffsetSheet + Offset <= 0 Then
OffsetSheet = CVErr(xlErrRef)
ElseIf OffsetSheet + Offset > Sheets.Count Then
OffsetSheet = CVErr(xlErrRef)
ElseIf Sheets(OffsetSheet + Offset).Type <> -4167 Then
OffsetSheet = CVErr(xlErrNA)
Else
OffsetSheet = Sheets(OffsetSheet + Offset) _
.Range(Range.Address).Value
End If
End Function

and this:

=OffsetSheet(-2,A$1)

Here the number “offset” detected the number of sheets forward or backwards (use a minus value) you what to reference. With a UDF such as this we can go to any worksheet in our workbook by changing the value of “offset”. One thing to watch out for here is none worksheet sheets – depending on how you wanted to control them, you could change the error handling.

Accompanying workbooks with examples

Links

Summing 3D sums with out VBA
http://www.thecodenet.com/articles.php?id=25&PHPSESSID=4782d8ca5e5d81571002efb2bcafd232