Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating sheet references
Id like to make different TEMPLATES for monthly ledgers where a result in a
cell for the previous month is displayed in the next month starting balance. There is a sheet for each month named January, February, March Ive managed to extract this monthly tab name as the header for each sheet with the formula =MID(CELL("filename";A1); FIND("]"; CELL("filename"; A1))+ 1; 255). Now Id like to use this name to find out the result for the previous month, i.e. from the previous sheet using a formula that calculates the sheet reference based on the name of the current sheet. F.i. in Sheet February, cell B2 the formula/reference =January!B100 would be computed based on the sheet February name. Yesterday Ron Coderre and others helped me to convert the month to its number. I tried with Rons monthly number formula, adding -1 to calculate the previous month name and use it as a reference but it doesnt work. The biggest obstacle seems to be concatenating f.i. the word February with the ! and the cell reference to a valid sheet/cell reference. Is it possible? If it is I can make one template and just copy the entire sheet and rename the tab and Voilá, the figures from the previous sheet are automatically inserted! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating sheet references
With the month of "January" as text in cell A1 you can come up with the prior
month's name like so: =TEXT(DATEVALUE(A1&" 1, 2007")-1,"MMMM") If that formula is in A2, then you can use =indirect(a2&"!B100) to pull in data from the prior month's sheet "Mats Samson" wrote: Id like to make different TEMPLATES for monthly ledgers where a result in a cell for the previous month is displayed in the next month starting balance. There is a sheet for each month named January, February, March Ive managed to extract this monthly tab name as the header for each sheet with the formula =MID(CELL("filename";A1); FIND("]"; CELL("filename"; A1))+ 1; 255). Now Id like to use this name to find out the result for the previous month, i.e. from the previous sheet using a formula that calculates the sheet reference based on the name of the current sheet. F.i. in Sheet February, cell B2 the formula/reference =January!B100 would be computed based on the sheet February name. Yesterday Ron Coderre and others helped me to convert the month to its number. I tried with Rons monthly number formula, adding -1 to calculate the previous month name and use it as a reference but it doesnt work. The biggest obstacle seems to be concatenating f.i. the word February with the ! and the cell reference to a valid sheet/cell reference. Is it possible? If it is I can make one template and just copy the entire sheet and rename the tab and Voilá, the figures from the previous sheet are automatically inserted! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating sheet references
Mats
If you're willing to use a User Defined Function....... Function PrevSheet(rg As Range) 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 20 sheets, sheet1 through sheet20...........sheet names don't matter. Select second sheet and SHIFT + Click last sheet In active sheet 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 Sep 2007 00:52:02 -0700, Mats Samson wrote: Id like to make different TEMPLATES for monthly ledgers where a result in a cell for the previous month is displayed in the next month starting balance. There is a sheet for each month named January, February, March Ive managed to extract this monthly tab name as the header for each sheet with the formula =MID(CELL("filename";A1); FIND("]"; CELL("filename"; A1))+ 1; 255). Now Id like to use this name to find out the result for the previous month, i.e. from the previous sheet using a formula that calculates the sheet reference based on the name of the current sheet. F.i. in Sheet February, cell B2 the formula/reference =January!B100 would be computed based on the sheet February name. Yesterday Ron Coderre and others helped me to convert the month to its number. I tried with Rons monthly number formula, adding -1 to calculate the previous month name and use it as a reference but it doesnt work. The biggest obstacle seems to be concatenating f.i. the word February with the ! and the cell reference to a valid sheet/cell reference. Is it possible? If it is I can make one template and just copy the entire sheet and rename the tab and Voil, the figures from the previous sheet are automatically inserted! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating sheet references
It took long time before I'd the possibility to work with this again,
but thank you guys for the help! I chose Gords UDF solution and it works perfectly. Cheers and keep up the good work! Mats "Gord Dibben" wrote: Mats If you're willing to use a User Defined Function....... Function PrevSheet(rg As Range) 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 20 sheets, sheet1 through sheet20...........sheet names don't matter. Select second sheet and SHIFT + Click last sheet In active sheet 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 Sep 2007 00:52:02 -0700, Mats Samson wrote: Id like to make different TEMPLATES for monthly ledgers where a result in a cell for the previous month is displayed in the next month starting balance. There is a sheet for each month named January, February, March Ive managed to extract this monthly tab name as the header for each sheet with the formula =MID(CELL("filename";A1); FIND("]"; CELL("filename"; A1))+ 1; 255). Now Id like to use this name to find out the result for the previous month, i.e. from the previous sheet using a formula that calculates the sheet reference based on the name of the current sheet. F.i. in Sheet February, cell B2 the formula/reference =January!B100 would be computed based on the sheet February name. Yesterday Ron Coderre and others helped me to convert the month to its number. I tried with Rons monthly number formula, adding -1 to calculate the previous month name and use it as a reference but it doesnt work. The biggest obstacle seems to be concatenating f.i. the word February with the ! and the cell reference to a valid sheet/cell reference. Is it possible? If it is I can make one template and just copy the entire sheet and rename the tab and Voilá, the figures from the previous sheet are automatically inserted! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating sheet references
Thanks Mats.
I believe the PrevSheet function was written by Chip Pearson. Gord On Fri, 28 Sep 2007 02:32:01 -0700, Mats Samson wrote: It took long time before I'd the possibility to work with this again, but thank you guys for the help! I chose Gords UDF solution and it works perfectly. Cheers and keep up the good work! Mats "Gord Dibben" wrote: Mats If you're willing to use a User Defined Function....... Function PrevSheet(rg As Range) 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 20 sheets, sheet1 through sheet20...........sheet names don't matter. Select second sheet and SHIFT + Click last sheet In active sheet 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 Sep 2007 00:52:02 -0700, Mats Samson wrote: Id like to make different TEMPLATES for monthly ledgers where a result in a cell for the previous month is displayed in the next month starting balance. There is a sheet for each month named January, February, March Ive managed to extract this monthly tab name as the header for each sheet with the formula =MID(CELL("filename";A1); FIND("]"; CELL("filename"; A1))+ 1; 255). Now Id like to use this name to find out the result for the previous month, i.e. from the previous sheet using a formula that calculates the sheet reference based on the name of the current sheet. F.i. in Sheet February, cell B2 the formula/reference =January!B100 would be computed based on the sheet February name. Yesterday Ron Coderre and others helped me to convert the month to its number. I tried with Rons monthly number formula, adding -1 to calculate the previous month name and use it as a reference but it doesnt work. The biggest obstacle seems to be concatenating f.i. the word February with the ! and the cell reference to a valid sheet/cell reference. Is it possible? If it is I can make one template and just copy the entire sheet and rename the tab and Voil, the figures from the previous sheet are automatically inserted! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a formula that references the last value of a column | Excel Discussion (Misc queries) | |||
Creating a formula that references the last value of a column | Excel Worksheet Functions | |||
Creating a formula that references the last value of a column | Excel Worksheet Functions | |||
creating cell references | Excel Discussion (Misc queries) | |||
Creating a formula that references other sheets | Excel Worksheet Functions |