Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name/number
Hello
I have a workbook with 12 monthly tabs(January-December (sheet 1- sheet12 respectively). Each sheet has begiinning and ending balances for different accounts. Is there a way to generically reference the previous month/sheet such as possibly using INDIRECT or Longre's sheetname() which I suppose is a short cut to using =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) Example: January!A1 Acct1 Beginning Balance January!A2 Acct1 Ending Balance February!A1 Acct1 Beginning Balance February!A2 Acct1 Ending Balance I would like to reference January Acct1 Ending Balance as February Acct1 Beginning Balance is a generic/indirect fashion. I hope someone can make sense of this Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name/number
Hi Sandy,
did you think to named the cells? (insert | name | defined) hth regards from Brazil Marcelo "Sandy" escreveu: Hello I have a workbook with 12 monthly tabs(January-December (sheet 1- sheet12 respectively). Each sheet has begiinning and ending balances for different accounts. Is there a way to generically reference the previous month/sheet such as possibly using INDIRECT or Longre's sheetname() which I suppose is a short cut to using =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) Example: January!A1 Acct1 Beginning Balance January!A2 Acct1 Ending Balance February!A1 Acct1 Beginning Balance February!A2 Acct1 Ending Balance I would like to reference January Acct1 Ending Balance as February Acct1 Beginning Balance is a generic/indirect fashion. I hope someone can make sense of this Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name/number
Hi Marcelo
Thanks for the reply. Im not sure how this would help because I would still have to reference the specific month. Im was hopiing something like thissheet -1 to get to the previous month. Where thissheet would be the sheetname or number. Sandy "Marcelo" wrote: Hi Sandy, did you think to named the cells? (insert | name | defined) hth regards from Brazil Marcelo "Sandy" escreveu: Hello I have a workbook with 12 monthly tabs(January-December (sheet 1- sheet12 respectively). Each sheet has begiinning and ending balances for different accounts. Is there a way to generically reference the previous month/sheet such as possibly using INDIRECT or Longre's sheetname() which I suppose is a short cut to using =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) Example: January!A1 Acct1 Beginning Balance January!A2 Acct1 Ending Balance February!A1 Acct1 Beginning Balance February!A2 Acct1 Ending Balance I would like to reference January Acct1 Ending Balance as February Acct1 Beginning Balance is a generic/indirect fashion. I hope someone can make sense of this Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name/number
you also culd use the indirect and address functions merged
eg. =indirecte(address(row number,col number, r1c1 ref, abs, worksheet name) try to see the address function help, course all information could be in a cell. ************** hth regards from Brazil Marcelo ************** "Sandy" escreveu: Hi Marcelo Thanks for the reply. Im not sure how this would help because I would still have to reference the specific month. Im was hopiing something like thissheet -1 to get to the previous month. Where thissheet would be the sheetname or number. Sandy "Marcelo" wrote: Hi Sandy, did you think to named the cells? (insert | name | defined) hth regards from Brazil Marcelo "Sandy" escreveu: Hello I have a workbook with 12 monthly tabs(January-December (sheet 1- sheet12 respectively). Each sheet has begiinning and ending balances for different accounts. Is there a way to generically reference the previous month/sheet such as possibly using INDIRECT or Longre's sheetname() which I suppose is a short cut to using =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) Example: January!A1 Acct1 Beginning Balance January!A2 Acct1 Ending Balance February!A1 Acct1 Beginning Balance February!A2 Acct1 Ending Balance I would like to reference January Acct1 Ending Balance as February Acct1 Beginning Balance is a generic/indirect fashion. I hope someone can make sense of this Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sheet name/number
Sandy
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 sheet31 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 On Thu, 6 Jul 2006 04:54:02 -0700, Sandy wrote: Hello I have a workbook with 12 monthly tabs(January-December (sheet 1- sheet12 respectively). Each sheet has begiinning and ending balances for different accounts. Is there a way to generically reference the previous month/sheet such as possibly using INDIRECT or Longre's sheetname() which I suppose is a short cut to using =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) Example: January!A1 Acct1 Beginning Balance January!A2 Acct1 Ending Balance February!A1 Acct1 Beginning Balance February!A2 Acct1 Ending Balance I would like to reference January Acct1 Ending Balance as February Acct1 Beginning Balance is a generic/indirect fashion. I hope someone can make sense of this Thanks! Gord Dibben MS Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Lookup function and compare | Excel Discussion (Misc queries) | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
Using a relative SHEET reference for source data in a chart | Charts and Charting in Excel | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions |