Sheet name/number

[Guest]

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!

 

[Guest]

Hi Sandy,

did you think to named the cells? (insert | name | defined)

hth
regards from Brazil
Marcelo

"Sandy" escreveu:

 

[Guest]

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

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!

 

[Guest]

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

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!

 

[Gord Dibben]

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


Gord Dibben Excel MVP

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