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:

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:

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.

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.

 

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:
 

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.

Links

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