| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I recalculate & move 3 cells data to the right in Excel?
I have setup the following 3 year project with total $300 budget:
b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel?
add a column for % for each year and put formulas into your row(1) to use the
% to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
Thank you for your reply. However, entering the formulas based on percentage
is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
OK so specifically what are you dealing with?
if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
Here is example #1:
b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $0 intend to spend in 2005 e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell h1 = empty cell Here is example #2: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell what I need to happen when I enter 40 into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $40 intend to spend in 2005 the remaining balance of ($300 - $40 spent in 2005 should be divided by the remaining 2 years left on this project) e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1) f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1) g1 = empty cell Here is example #3: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $0 intend to spend in 2005 e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30 h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15 i1 = empty cell Here is example #4: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 11 into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $11 intend to spend in 2005 the remaining balance of ($100 - $11 spent in 2005 should be divided by the remaining 3 years left on this project as follows: 15% should be paid in the last year of the project €“ 2008 and the remaining amount should be divided equally among other years, 2006 & 2007 in this example) e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2) f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2) g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell I have some other combinations. But these 2 are the most common. Thank you. "Vacation's Over" wrote: OK so specifically what are you dealing with? if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
Only thing that comes to mind would be to build a VBA app with a pop up
userform to input actual and choose how to "spread" the difference. then drop resulting array into the range. unreasonably complex for this forum 4 hours for a local VBA jock, or http://j-walk.com buy John's book and figure it out yourself. Good luck "Vic" wrote: Here is example #1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $0 intend to spend in 2005 e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell h1 = empty cell Here is example #2: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell what I need to happen when I enter 40 into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $40 intend to spend in 2005 the remaining balance of ($300 - $40 spent in 2005 should be divided by the remaining 2 years left on this project) e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1) f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1) g1 = empty cell Here is example #3: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $0 intend to spend in 2005 e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30 h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15 i1 = empty cell Here is example #4: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 11 into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $11 intend to spend in 2005 the remaining balance of ($100 - $11 spent in 2005 should be divided by the remaining 3 years left on this project as follows: 15% should be paid in the last year of the project €“ 2008 and the remaining amount should be divided equally among other years, 2006 & 2007 in this example) e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2) f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2) g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell I have some other combinations. But these 2 are the most common. Thank you. "Vacation's Over" wrote: OK so specifically what are you dealing with? if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
All I wanted was to find out if there was a process/program/language in excel
that could be triggered by a change in one single cell and was there a method to locate the next empty cell on the right? Everything else I could code myself with sets of formulas and if statements. Thank you. "Vacation's Over" wrote: Only thing that comes to mind would be to build a VBA app with a pop up userform to input actual and choose how to "spread" the difference. then drop resulting array into the range. unreasonably complex for this forum 4 hours for a local VBA jock, or http://j-walk.com buy John's book and figure it out yourself. Good luck "Vic" wrote: Here is example #1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $0 intend to spend in 2005 e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell h1 = empty cell Here is example #2: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell what I need to happen when I enter 40 into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $40 intend to spend in 2005 the remaining balance of ($300 - $40 spent in 2005 should be divided by the remaining 2 years left on this project) e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1) f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1) g1 = empty cell Here is example #3: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $0 intend to spend in 2005 e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30 h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15 i1 = empty cell Here is example #4: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 11 into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $11 intend to spend in 2005 the remaining balance of ($100 - $11 spent in 2005 should be divided by the remaining 3 years left on this project as follows: 15% should be paid in the last year of the project €“ 2008 and the remaining amount should be divided equally among other years, 2006 & 2007 in this example) e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2) f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2) g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell I have some other combinations. But these 2 are the most common. Thank you. "Vacation's Over" wrote: OK so specifically what are you dealing with? if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
Yes in VBA you can do those things.
My point is that although VBA is not "hard" it must be understood or you open a can of worms. and spend time. one book from amazon, + 4 hour review and copies of code that come woith and you will be amazed at what you can do. specifically the worksheet change event is fired when any cell value is changed so you could easily put code there to activate when the cell changed is in your target column. range.end(XLright) will take you from whaere you are to the last contigious used cell to the right offset(0,1) will move from there to the open cell to the right best wishes "Vic" wrote: All I wanted was to find out if there was a process/program/language in excel that could be triggered by a change in one single cell and was there a method to locate the next empty cell on the right? Everything else I could code myself with sets of formulas and if statements. Thank you. "Vacation's Over" wrote: Only thing that comes to mind would be to build a VBA app with a pop up userform to input actual and choose how to "spread" the difference. then drop resulting array into the range. unreasonably complex for this forum 4 hours for a local VBA jock, or http://j-walk.com buy John's book and figure it out yourself. Good luck "Vic" wrote: Here is example #1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $0 intend to spend in 2005 e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell h1 = empty cell Here is example #2: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell what I need to happen when I enter 40 into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $40 intend to spend in 2005 the remaining balance of ($300 - $40 spent in 2005 should be divided by the remaining 2 years left on this project) e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1) f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1) g1 = empty cell Here is example #3: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $0 intend to spend in 2005 e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30 h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15 i1 = empty cell Here is example #4: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 11 into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $11 intend to spend in 2005 the remaining balance of ($100 - $11 spent in 2005 should be divided by the remaining 3 years left on this project as follows: 15% should be paid in the last year of the project €“ 2008 and the remaining amount should be divided equally among other years, 2006 & 2007 in this example) e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2) f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2) g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell I have some other combinations. But these 2 are the most common. Thank you. "Vacation's Over" wrote: OK so specifically what are you dealing with? if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
What is the exact name of John's book that I need to get? He has 53 titles on
Excel at Amazon.com Thank you. "Vacation's Over" wrote: Yes in VBA you can do those things. My point is that although VBA is not "hard" it must be understood or you open a can of worms. and spend time. one book from amazon, + 4 hour review and copies of code that come woith and you will be amazed at what you can do. specifically the worksheet change event is fired when any cell value is changed so you could easily put code there to activate when the cell changed is in your target column. range.end(XLright) will take you from whaere you are to the last contigious used cell to the right offset(0,1) will move from there to the open cell to the right best wishes "Vic" wrote: All I wanted was to find out if there was a process/program/language in excel that could be triggered by a change in one single cell and was there a method to locate the next empty cell on the right? Everything else I could code myself with sets of formulas and if statements. Thank you. "Vacation's Over" wrote: Only thing that comes to mind would be to build a VBA app with a pop up userform to input actual and choose how to "spread" the difference. then drop resulting array into the range. unreasonably complex for this forum 4 hours for a local VBA jock, or http://j-walk.com buy John's book and figure it out yourself. Good luck "Vic" wrote: Here is example #1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $0 intend to spend in 2005 e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell h1 = empty cell Here is example #2: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell what I need to happen when I enter 40 into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $40 intend to spend in 2005 the remaining balance of ($300 - $40 spent in 2005 should be divided by the remaining 2 years left on this project) e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1) f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1) g1 = empty cell Here is example #3: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $0 intend to spend in 2005 e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30 h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15 i1 = empty cell Here is example #4: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 11 into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $11 intend to spend in 2005 the remaining balance of ($100 - $11 spent in 2005 should be divided by the remaining 3 years left on this project as follows: 15% should be paid in the last year of the project €“ 2008 and the remaining amount should be divided equally among other years, 2006 & 2007 in this example) e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2) f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2) g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell I have some other combinations. But these 2 are the most common. Thank you. "Vacation's Over" wrote: OK so specifically what are you dealing with? if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do I recalculate & move 3 cells data to the right in Excel
I suggest
Excel 2003 Power programing with VBA great code samples come with it on CD "Vic" wrote: What is the exact name of John's book that I need to get? He has 53 titles on Excel at Amazon.com Thank you. "Vacation's Over" wrote: Yes in VBA you can do those things. My point is that although VBA is not "hard" it must be understood or you open a can of worms. and spend time. one book from amazon, + 4 hour review and copies of code that come woith and you will be amazed at what you can do. specifically the worksheet change event is fired when any cell value is changed so you could easily put code there to activate when the cell changed is in your target column. range.end(XLright) will take you from whaere you are to the last contigious used cell to the right offset(0,1) will move from there to the open cell to the right best wishes "Vic" wrote: All I wanted was to find out if there was a process/program/language in excel that could be triggered by a change in one single cell and was there a method to locate the next empty cell on the right? Everything else I could code myself with sets of formulas and if statements. Thank you. "Vacation's Over" wrote: Only thing that comes to mind would be to build a VBA app with a pop up userform to input actual and choose how to "spread" the difference. then drop resulting array into the range. unreasonably complex for this forum 4 hours for a local VBA jock, or http://j-walk.com buy John's book and figure it out yourself. Good luck "Vic" wrote: Here is example #1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $0 intend to spend in 2005 e1 = $100 intend to spend in 2006, formula: b1/c1 moved from former d1 cell f1 = $100 intend to spend in 2007, formula: b1/c1 moved from former e1 cell g1 = $100 intend to spend in 2008, formula: b1/c1 moved from former f1 cell h1 = empty cell Here is example #2: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $100 intend to spend in 2005, formula: b1/c1 e1 = $100 intend to spend in 2006, formula: b1/c1 f1 = $100 intend to spend in 2007, formula: b1/c1 g1 = empty cell what I need to happen when I enter 40 into d1: b1 = $300 total project cost c1 = 3 number of years for this project d1 = $40 intend to spend in 2005 the remaining balance of ($300 - $40 spent in 2005 should be divided by the remaining 2 years left on this project) e1 = $130 intend to spend in 2006, formula: (b1-40)/(c1-1) f1 = $130 intend to spend in 2007, formula: (b1-40)/(c1-1) g1 = empty cell Here is example #3: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 0 (zero) into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $0 intend to spend in 2005 e1 = $30 intend to spend in 2006 (25% of budget), formula: b1*0.25 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $30 intend to spend in 2008 (30% of budget), formula: b1*0.30 h1 = $15 intend to spend in 2009 (15% of budget), formula: b1*0.15 i1 = empty cell Here is example #4: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $25 intend to spend in 2005 (25% of budget), formula: b1*0.25 e1 = $30 intend to spend in 2006 (30% of budget), formula: b1*0.30 f1 = $30 intend to spend in 2007 (30% of budget), formula: b1*0.30 g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell What I need to happen when I enter 11 into d1: b1 = $100 total project cost c1 = 4 number of years for this project d1 = $11 intend to spend in 2005 the remaining balance of ($100 - $11 spent in 2005 should be divided by the remaining 3 years left on this project as follows: 15% should be paid in the last year of the project €“ 2008 and the remaining amount should be divided equally among other years, 2006 & 2007 in this example) e1 = $37 intend to spend in 2006, formula: (b1-d1-g1)/(c1-2) f1 = $37 intend to spend in 2007, formula: (b1-d1-g1)/(c1-2) g1 = $15 intend to spend in 2008 (15% of budget), formula: b1*0.15 h1 = empty cell I have some other combinations. But these 2 are the most common. Thank you. "Vacation's Over" wrote: OK so specifically what are you dealing with? if it was simple you could just drag and drop and maintain all formulas. if it is complicated how would a macro know that you did not spend in this year and want to move? please think it through and provide a few use cases "Vic" wrote: Thank you for your reply. However, entering the formulas based on percentage is NOT the issue here. I need to be able to shift my budget forecasts to next years starting from the first empty cell on the right. For example: I intended to spend $100 in 2005, $100 in 2006 & $100 in 2007 for a 3-year $300 project. Now, at the end of 2005, I can't spend the first $100, so I want to move my project to span from 2006 thru 2008 (2008 would be the empty cell on the right). All I want is to shift my budget 1 year over to the right. It is easy to manually re-enter $100, but my budgets are very complicated and I use many formulas. I can do this manually by moving cells to the right and enter $0 in 2005. However, it is not practical. I am looking for a process to automate that. "Vacation's Over" wrote: add a column for % for each year and put formulas into your row(1) to use the % to generate teh cash flows "Vic" wrote: I have setup the following 3 year project with total $300 budget: b1=$300, c1=3years, d1=$100, e1=$100, f1=$100, g1 is empty if I change the value in d1 to $0 (meaning, I don't intent to spend any money this year), I need excel to find the next empty cell which is g1 and move data from f1 to g1, from e1 to f1 and from former d1 to e1. This is an oversimplified version of what I need to accomplish. I have 50 projects with different year lenghts and money not always distributed as 1/3, 1/3 & 1/3. I could have 25%, 30%, 30% & 15% for a 4-year project and other combinations. Does anyone know how to automate this procedure so I could setup 1 formula for each project and forecast my spendings by changing amount in the current year (d1)? Any help would be greatly appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I don't want all NOW() cells to recalculate | Excel Worksheet Functions | |||
| move data from multiple cells in one row to another | Excel Worksheet Functions | |||
| recalculate cells | Excel Worksheet Functions | |||
| How do I recalculate a range of cells | Excel Worksheet Functions | |||
| Move data from cells to form? Help! | Excel Programming | |||
Linear Mode
