I have a workbook that I create a new worksheet in each week.
These worksheets have a coulmn for each day of the week, then 3 columns
"Weekly Total", "Previous Total", "Total To Date".

Is there a way to have the Previous Total on the new sheet automatically
know that I am refering to the very last sheet?

Right now I right click on the tab copy the sheet and then click in each
cell in column K to update last weeks sheet column L
(for example ='1'!L7 is the formula in cell K7 sheet 2).

I use this workbook to track project quantities added to a job
(construction) then I have to transfer the these totals to quickbooks and to
pay applications and a few other places before I can mail out our bills to
the prime contractors we work for. It's all so time consuming so I was hoping
someone may know a secret to help me so I dont have to copy and paste and
click and retype each formula

Hopefully I am explaining my problem correctly.

--
Thank you for your help

You can use an INDIRECT formula to do this, but it must be constructed
carefully. You say your sheets are named 1, 2, 3, etc, and this critical for
this to work.

Since your sheetname are numeric, this formula will return that numeric value:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
(note, that will only work after you've save your sheet at least once)

If on sheet 1!K7 you have a value you wish to bring onto sheet 2, then this
formula will create that reference on sheet "2" by looking up its own sheet
name, subtracting one from the numeric value, then the INDIRECT adds the rest
of the cell reference.

=INDIRECT(MID(CELL("filename"),FIND("]",
CELL("filename"))+1,255)-1&"!K7")

Each sheet you copy that to will always look back one sheet (based on
sheetname -1) and give you the value in cell K7.

Hope this helps.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"Emily W" wrote:

I have a workbook that I create a new worksheet in each week.
These worksheets have a coulmn for each day of the week, then 3 columns
"Weekly Total", "Previous Total", "Total To Date".

Is there a way to have the Previous Total on the new sheet automatically
know that I am refering to the very last sheet?

Right now I right click on the tab copy the sheet and then click in each
cell in column K to update last weeks sheet column L
(for example ='1'!L7 is the formula in cell K7 sheet 2).

I use this workbook to track project quantities added to a job
(construction) then I have to transfer the these totals to quickbooks and to
pay applications and a few other places before I can mail out our bills to
the prime contractors we work for. It's all so time consuming so I was hoping
someone may know a secret to help me so I dont have to copy and paste and
click and retype each formula

Hopefully I am explaining my problem correctly.

--
Thank you for your help

Hi,

You can create the following custom function:

Function Previous()
Previous = Sheets(ActiveSheet.Index - 1).Name
End Function

The in a cell of the any sheet enter the formula

=Previous()

It will return the previous sheet's name.
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Emily W" wrote:

I have a workbook that I create a new worksheet in each week.
These worksheets have a coulmn for each day of the week, then 3 columns
"Weekly Total", "Previous Total", "Total To Date".

Is there a way to have the Previous Total on the new sheet automatically
know that I am refering to the very last sheet?

Right now I right click on the tab copy the sheet and then click in each
cell in column K to update last weeks sheet column L
(for example ='1'!L7 is the formula in cell K7 sheet 2).

I use this workbook to track project quantities added to a job
(construction) then I have to transfer the these totals to quickbooks and to
pay applications and a few other places before I can mail out our bills to
the prime contractors we work for. It's all so time consuming so I was hoping
someone may know a secret to help me so I dont have to copy and paste and
click and retype each formula

Hopefully I am explaining my problem correctly.

--
Thank you for your help

=cell("filename")

will return the name of the active sheet in the active workbook when excel
recalculated.

If you want the name of the worksheet that contains the formula, you'll want to
include a cell reference in the expression:

=cell("filename",a1)
(I like to use the cell that contains the formula)

And if the worksheet names are really 1, 2, 3, ...

Then you'd need to surround the name with single quotes:

=INDIRECT("'"&MID(CELL("filename",A1),
FIND("]",CELL("filename",A1))+1,255)-1&"'!K7")

And the workbook has to be saved at least once.



JBeaucaire wrote:

You can use an INDIRECT formula to do this, but it must be constructed
carefully. You say your sheets are named 1, 2, 3, etc, and this critical for
this to work.

Since your sheetname are numeric, this formula will return that numeric value:

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)
(note, that will only work after you've save your sheet at least once)

If on sheet 1!K7 you have a value you wish to bring onto sheet 2, then this
formula will create that reference on sheet "2" by looking up its own sheet
name, subtracting one from the numeric value, then the INDIRECT adds the rest
of the cell reference.

=INDIRECT(MID(CELL("filename"),FIND("]",
CELL("filename"))+1,255)-1&"!K7")

Each sheet you copy that to will always look back one sheet (based on
sheetname -1) and give you the value in cell K7.

Hope this helps.
--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Emily W" wrote:

I have a workbook that I create a new worksheet in each week.
These worksheets have a coulmn for each day of the week, then 3 columns
"Weekly Total", "Previous Total", "Total To Date".

Is there a way to have the Previous Total on the new sheet automatically
know that I am refering to the very last sheet?

Right now I right click on the tab copy the sheet and then click in each
cell in column K to update last weeks sheet column L
(for example ='1'!L7 is the formula in cell K7 sheet 2).

I use this workbook to track project quantities added to a job
(construction) then I have to transfer the these totals to quickbooks and to
pay applications and a few other places before I can mail out our bills to
the prime contractors we work for. It's all so time consuming so I was hoping
someone may know a secret to help me so I dont have to copy and paste and
click and retype each formula

Hopefully I am explaining my problem correctly.

--
Thank you for your help

--

Dave Peterson