Creating sheet references

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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

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
 
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 said:
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!
Click to expand...
Click to expand...
 
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

Gord Dibben said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top