If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each
worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. -- Thanks Mike |
#2
|
|||
|
|||
Transfering a changing cell value progressively through a workbook
If you're willing to use a User Defined Function this becomes quite
easy....... 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 Example of use...................... Say you have 12 sheets, sheet1 through sheet12...........sheet names don't matter. In sheet1 you have a formula in A10 =SUM(A1:A9) Select second sheet and SHIFT + Click last sheet In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9) Ungroup the sheets. Each A10 will have the sum of the previous sheet's A10 plus the sum of the current sheet's A1:A9 Gord Dibben MS Excel MVP On Wed, 3 Feb 2010 18:33:01 -0800, Mike1558 wrote: I am creating a payment application form in excel 2007 using windows 7. Each worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. |
#3
|
|||
|
|||
Transfering a changing cell value progressively through a work
I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows
up in the cell is #REF!. I have been unable to make this produce a value. What did I do wrong? I copied the UDF to vis basic like you discribed in the previous email but there must be something that I did wrong. Thanks Mike -- Thanks Mike "Gord Dibben" wrote: If you're willing to use a User Defined Function this becomes quite easy....... 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 Example of use...................... Say you have 12 sheets, sheet1 through sheet12...........sheet names don't matter. In sheet1 you have a formula in A10 =SUM(A1:A9) Select second sheet and SHIFT + Click last sheet In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9) Ungroup the sheets. Each A10 will have the sum of the previous sheet's A10 plus the sum of the current sheet's A1:A9 Gord Dibben MS Excel MVP On Wed, 3 Feb 2010 18:33:01 -0800, Mike1558 wrote: I am creating a payment application form in excel 2007 using windows 7. Each worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. . |
#4
|
|||
|
|||
Transfering a changing cell value progressively through a work
Only way I can get #REF! is if I enter the formula on first sheet.
There is no previous sheet in that case. Gord On Thu, 4 Feb 2010 12:50:10 -0800, Mike1558 wrote: I tried this and what I get in the cell that I enter PrevSheet(Q7)what shows up in the cell is #REF!. I have been unable to make this produce a value. What did I do wrong? I copied the UDF to vis basic like you discribed in the previous email but there must be something that I did wrong. Thanks Mike |
Thread Tools | |
Display Modes | |
|
|