Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative worksheet reference clarified
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative worksheet reference clarified
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) On Sat, 29 Aug 2009 16:43:01 -0700, Debbie wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative worksheet reference clarified
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. "Debbie" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative worksheet reference clarified
And you have another response at your other post.
Debbie wrote: 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. -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Relative worksheet reference clarified
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) On Sat, 29 Aug 2009 16:43:01 -0700, Debbie wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative Reference help | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
If/Or Clarified | Excel Worksheet Functions | |||
Relative worksheet reference in 3-D formulas? | Excel Worksheet Functions | |||
Relative Reference | Excel Discussion (Misc queries) |