Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
I am creating a budget/pay-application template that uses a new worksheet for
each pay-application and the number of worksheets corrisponds to the duration of the job. Within these worksheets are cells that contain formulas that produce values and these values need to be entered into a formula that creats an new value for next pay-application period. Does anyone know how to do this, any help will be greatly appreciated. -- Thanks Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
Only with a LOT more information?
-- HTH Kassie Replace xxx with hotmail "Mike1558" wrote: I am creating a budget/pay-application template that uses a new worksheet for each pay-application and the number of worksheets corrisponds to the duration of the job. Within these worksheets are cells that contain formulas that produce values and these values need to be entered into a formula that creats an new value for next pay-application period. Does anyone know how to do this, any help will be greatly appreciated. -- Thanks Mike |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
What I am doing is, say for the pay-app month May, I have a cell that
contains the total amount of a line item for this draw period. The next cell in the row contains a formula that gives the results for the amount for this line item spent to date. To one of the values in "spent to date" cell comes from the same cell in a previous worksheet "April". Once the "May" worksheet is done and I move on to create the "June" worksheet the "May" amount now becomes a value in the "June" worksheet. This happens progressively through the completion of the job. Do you have a suggestion? Would it be helpful to see what I am working on? I really appreciate any help you can give me. Also I am having an annoying problem with all my excel workbooks, I have office 2007, when I try to select a single cell it selects multiple cells then what I do is move the curser to the lower right hand corner of the cell that I want to select and I have to keep clicking on it until the single cell that I want is selected. This usually takes several attemps because a cell in the vacinity of the cell that I want gets selected not the cell that I want. Any suggestions? -- Thanks Mike "Kassie" wrote: Only with a LOT more information? -- HTH Kassie Replace xxx with hotmail "Mike1558" wrote: I am creating a budget/pay-application template that uses a new worksheet for each pay-application and the number of worksheets corrisponds to the duration of the job. Within these worksheets are cells that contain formulas that produce values and these values need to be entered into a formula that creats an new value for next pay-application period. Does anyone know how to do this, any help will be greatly appreciated. -- Thanks Mike |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
Problem number 1..........updating formulas to refer to previous month.
Copy/paste this UDF to a general module in your workbook. 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 usage................... 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 Problem number 2..............selection of multiple cells Try setting the Zoom level a little higher or lower to stop the multiple cell selecting. Gord Dibben MS Excel MVP On Sat, 27 Jun 2009 07:30:01 -0700, Mike1558 wrote: What I am doing is, say for the pay-app month May, I have a cell that contains the total amount of a line item for this draw period. The next cell in the row contains a formula that gives the results for the amount for this line item spent to date. To one of the values in "spent to date" cell comes from the same cell in a previous worksheet "April". Once the "May" worksheet is done and I move on to create the "June" worksheet the "May" amount now becomes a value in the "June" worksheet. This happens progressively through the completion of the job. Do you have a suggestion? Would it be helpful to see what I am working on? I really appreciate any help you can give me. Also I am having an annoying problem with all my excel workbooks, I have office 2007, when I try to select a single cell it selects multiple cells then what I do is move the curser to the lower right hand corner of the cell that I want to select and I have to keep clicking on it until the single cell that I want is selected. This usually takes several attemps because a cell in the vacinity of the cell that I want gets selected not the cell that I want. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
Gord
Thanks for your help, I understand what you are doing in the second half of problem 1 but am unsure of what you want me to do in the 1st half of problem 1. What is a UDF and a general module of a work book and then what does it do and how do I use it? I am guessing that this is code that is to be entered into the workbook but I am unsure of how to do it. -- Thanks again Mike "Gord Dibben" wrote: Problem number 1..........updating formulas to refer to previous month. Copy/paste this UDF to a general module in your workbook. 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 usage................... 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 Problem number 2..............selection of multiple cells Try setting the Zoom level a little higher or lower to stop the multiple cell selecting. Gord Dibben MS Excel MVP On Sat, 27 Jun 2009 07:30:01 -0700, Mike1558 wrote: What I am doing is, say for the pay-app month May, I have a cell that contains the total amount of a line item for this draw period. The next cell in the row contains a formula that gives the results for the amount for this line item spent to date. To one of the values in "spent to date" cell comes from the same cell in a previous worksheet "April". Once the "May" worksheet is done and I move on to create the "June" worksheet the "May" amount now becomes a value in the "June" worksheet. This happens progressively through the completion of the job. Do you have a suggestion? Would it be helpful to see what I am working on? I really appreciate any help you can give me. Also I am having an annoying problem with all my excel workbooks, I have office 2007, when I try to select a single cell it selects multiple cells then what I do is move the curser to the lower right hand corner of the cell that I want to select and I have to keep clicking on it until the single cell that I want is selected. This usually takes several attemps because a cell in the vacinity of the cell that I want gets selected not the cell that I want. Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
If you're not familiar with VBA and macros, see David McRitchie's site for
more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. 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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. From there, follow original instructions after these lines. Example of usage................... Say you have 12 sheets, sheet1 through sheet12...........sheet names don't matter. Gord On Sat, 27 Jun 2009 10:08:01 -0700, Mike1558 wrote: Gord Thanks for your help, I understand what you are doing in the second half of problem 1 but am unsure of what you want me to do in the 1st half of problem 1. What is a UDF and a general module of a work book and then what does it do and how do I use it? I am guessing that this is code that is to be entered into the workbook but I am unsure of how to do it. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
Gord
I have been trying (in my spare time) to get this to work. I have copied the UDF as you discribed but what is now happening is that I have 6 cells that reference the value in A10 and A10 as well as the 6 cells all display #REF!. Worksheet 1 has a value in it and the following sheets that I have used the PrevSheet function all display the #REF!. Have I missed something? I realy apreciate your help as this has the potenial to be a big time saver in future templates. -- Thanks Mike "Gord Dibben" wrote: If you're not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm or Ron de De Bruin's site on where to store macros. http://www.rondebruin.nl/code.htm In the meantime.......... First...create a backup copy of your original workbook. 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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. From there, follow original instructions after these lines. Example of usage................... Say you have 12 sheets, sheet1 through sheet12...........sheet names don't matter. Gord On Sat, 27 Jun 2009 10:08:01 -0700, Mike1558 wrote: Gord Thanks for your help, I understand what you are doing in the second half of problem 1 but am unsure of what you want me to do in the 1st half of problem 1. What is a UDF and a general module of a work book and then what does it do and how do I use it? I am guessing that this is code that is to be entered into the workbook but I am unsure of how to do it. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
The error could mean there is no previous sheet.
The error could mean that one of the previous sheets has a #REF! error and is propagating across. Try opening a new workbook, add the UDF to a general module. Enter qwerty in A1 of Sheet1 Enter =Prevsheet(A1) in A1 of Sheet2 OK so far? Work through from there. OR send me the workbook with some explanation of which cells you want to refer to previous sheet. gorddibbATshawDOTca change the obvious. Gord On Tue, 6 Apr 2010 05:58:07 -0700, Mike1558 wrote: Gord I have been trying (in my spare time) to get this to work. I have copied the UDF as you discribed but what is now happening is that I have 6 cells that reference the value in A10 and A10 as well as the 6 cells all display #REF!. Worksheet 1 has a value in it and the following sheets that I have used the PrevSheet function all display the #REF!. Have I missed something? I realy apreciate your help as this has the potenial to be a big time saver in future templates. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
transfering cell values progressively through worksheets
Gord
I emailed you my file let me know if it comes through -- Thanks Mike "Gord Dibben" wrote: The error could mean there is no previous sheet. The error could mean that one of the previous sheets has a #REF! error and is propagating across. Try opening a new workbook, add the UDF to a general module. Enter qwerty in A1 of Sheet1 Enter =Prevsheet(A1) in A1 of Sheet2 OK so far? Work through from there. OR send me the workbook with some explanation of which cells you want to refer to previous sheet. gorddibbATshawDOTca change the obvious. Gord On Tue, 6 Apr 2010 05:58:07 -0700, Mike1558 wrote: Gord I have been trying (in my spare time) to get this to work. I have copied the UDF as you discribed but what is now happening is that I have 6 cells that reference the value in A10 and A10 as well as the 6 cells all display #REF!. Worksheet 1 has a value in it and the following sheets that I have used the PrevSheet function all display the #REF!. Have I missed something? I realy apreciate your help as this has the potenial to be a big time saver in future templates. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfering only specific data between worksheets | Excel Worksheet Functions | |||
Transfering Values | Excel Worksheet Functions | |||
Transfering data across worksheets | Excel Discussion (Misc queries) | |||
copy numbers progressively | Excel Worksheet Functions | |||
Transfering and totalling corresponding data to other worksheets | Excel Discussion (Misc queries) |