Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Relative Sheets
How do I reference the worksheet immediately preceeding the current
worksheet? I would then like to copy that formula onto the next worksheet, so that it automatically adjusts to reference the current worksheet (which is its preceeding worksheet). |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Relative Sheets
"jwatters" wrote: How do I reference the worksheet immediately preceeding the current worksheet? I would then like to copy that formula onto the next worksheet, so that it automatically adjusts to reference the current worksheet (which is its preceeding worksheet). |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Relative Sheets
I've done this very thing using INDIRECT function, but to be able to smoothly work, your worksheets need to be named in a numerical way. Simplest is to just name the sheets 1, 2, 3, etc... Then use INDIRECT to build up a cell reference missing the sheetname itself, then use any standard progressive method to increment from sheet to sheet based on the existing sheetname. I did this very thing on the attached spreadsheet to keep a running total going from sheet to sheet without needing to adjust the "totals" formula each time. The formula in cell C12 adds C11 on this sheet to C12 from the previous sheet. =C11+INDIRECT((MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,256)-1)&\"!\"&\"C12\") +-------------------------------------------------------------------+ |Filename: Running Total.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=57| +-------------------------------------------------------------------+ -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46213 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Relative Sheets
=C11+INDIRECT((MID(CELL(\"filename\",A1),FIND(\ "]\",CELL(\"filename\",A1))+1,256)-1)&\"!\"&\"C12\")
Here's the formula with all the web formatting junk removed: =C11+INDIRECT((MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)-1)&"!"&"C12") -- Biff Microsoft Excel MVP "JBeaucaire" wrote in message ... I've done this very thing using INDIRECT function, but to be able to smoothly work, your worksheets need to be named in a numerical way. Simplest is to just name the sheets 1, 2, 3, etc... Then use INDIRECT to build up a cell reference missing the sheetname itself, then use any standard progressive method to increment from sheet to sheet based on the existing sheetname. I did this very thing on the attached spreadsheet to keep a running total going from sheet to sheet without needing to adjust the "totals" formula each time. The formula in cell C12 adds C11 on this sheet to C12 from the previous sheet. =C11+INDIRECT((MID(CELL(\"filename\",A1),FIND(\"]\",CELL(\"filename\",A1))+1,256)-1)&\"!\"&\"C12\") +-------------------------------------------------------------------+ |Filename: Running Total.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=57| +-------------------------------------------------------------------+ -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=46213 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referencing Relative Sheets
I found this response in another similar inquiry --- this resolved my problem:
========================================== If you're willing to use a User Defined Function....... Function PrevSheet(rg As Range) 'Enter =PrevSheet(B1) on sheet2 and you'll get B1 from sheet1. n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function Say you have 12 sheets, sheet1 through sheet12. Select sheet2 and SHIFT + Click sheet12 In B1 enter =PrevSheet(A1) Ungroup the sheets. Each B1 will have the contents of the previous sheet's A1 Copy/paste the UDF above into a General Module in your workbook. If not familiar with macros and VBA, visit David McRitchie's website on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the above code in there. Save the workbook and hit ALT + Q to return to your workbook. Gord Dibben Excel MVP ======================================== "jwatters" wrote: How do I reference the worksheet immediately preceeding the current worksheet? I would then like to copy that formula onto the next worksheet, so that it automatically adjusts to reference the current worksheet (which is its preceeding worksheet). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relative worksheet referencing | Excel Discussion (Misc queries) | |||
Relative vs Absolute referencing of Workbooks | Excel Worksheet Functions | |||
Relative column referencing within formulas | Excel Discussion (Misc queries) | |||
macro relative referencing | Excel Discussion (Misc queries) | |||
Relative Indirect Formula Referencing? | Excel Worksheet Functions |