Creating sheet references

[Guest]

I’d 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……
I’ve 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 I’d 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 doesn’t 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!

 

[Guest]

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

 

[Gord Dibben]

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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

 

[Guest]

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

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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

I’d 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……
I’ve 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 I’d 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 doesn’t 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!

 

[Gord Dibben]

Thanks Mats.

I believe the PrevSheet function was written by Chip Pearson.


Gord

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

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 Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.


Gord Dibben Excel MVP

I’d 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……
I’ve 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 I’d 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 doesn’t 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!