General Info
I'm using Windows 7, Excel 2010
Model Background
I have a model that has various hard coded assumptions (volume, price, cost, etc...) and then various calculations (i.e. Volume * Price = Revenue, etc...). Each unique assumption or calculation is allowed to have up to 50 rows of input. These 50 rows make up a unique section within the model. Each unique “section” or cell range has 50 rows and 48 columns.
Examples of “Sections” as found on Original worksheet (attached file)
The very first section is the master section (volume in the attached file) and this is where the user will define (or hard code) the data found in columns A – U (i.e. A2:U51). Each of the other sections found below the master all link into the master section essentially replicating the descriptor values found in the master section to all other sections. You’ll also notice that Column A has the values 1-50 and this is the same in all sections. The idea is that all the #1’s in column A are tied together for all descriptors and then also the hard coded assumptions and formulas found in columns V – AV.
Needed Functionality
A user will be using this model and have data populated for values 1-20 (column A) for all sections. The user will then get additional information that will require them to refine the model. For clarify the attached file goes through single scenario in which the user wants to add 3 new rows below #10 (in column A) for all sections (volume, price, revenue).
In the attached file you'll find three worksheets.
So in summary I’m looking code that looks in column A and finds value = 10 (value specified by the user) in section 1 and then adds a user specified # of new rows below value #10, then it would go to section 2 and find value 10 and add the same amount of user specified rows below value 10 in that section and continue to loop through all sections found in the model to add the new rows to all sections in the same place within each section. Then if possible updated the values in column A so the numbers go from 1-53 or 1-58 if the user says they wanted to add 8 rows instead of 3. Then lastly copy and paste the formulas found in row 10 to the newly created rows below #10.
If there’s a real easy way to complete what I’m asking please just let me know. This can be done manually but is very time consuming and user acceptance of the manual approach is very low. I’ve searched the forum and the net and found code that lets you add a specified row at various points (i.e. every 10th row) but nothing that quite does exactly what I'm trying to do. Then I've also found a basic idea on trying to use sorting to accomplish this task but so far I haven't found a solution that works.
Thanks in advance for any help. Any questions let me know.
Hmm...apparently you can't post attachments. Well hopefully the above details are detailed enough to understand what the model looks like and what I’m trying to do. I’ll dig around a little more to see how I can get my example file more visible to everyone. Any advice on this let me know.
I'm using Windows 7, Excel 2010
Model Background
I have a model that has various hard coded assumptions (volume, price, cost, etc...) and then various calculations (i.e. Volume * Price = Revenue, etc...). Each unique assumption or calculation is allowed to have up to 50 rows of input. These 50 rows make up a unique section within the model. Each unique “section” or cell range has 50 rows and 48 columns.
Examples of “Sections” as found on Original worksheet (attached file)
- Volume Section = A2:AV51 (FYI, I currently have a blank row between each section)
- Price Section = A53:AV102
- Revenue Calc Section = A104:AV153
The very first section is the master section (volume in the attached file) and this is where the user will define (or hard code) the data found in columns A – U (i.e. A2:U51). Each of the other sections found below the master all link into the master section essentially replicating the descriptor values found in the master section to all other sections. You’ll also notice that Column A has the values 1-50 and this is the same in all sections. The idea is that all the #1’s in column A are tied together for all descriptors and then also the hard coded assumptions and formulas found in columns V – AV.
Needed Functionality
A user will be using this model and have data populated for values 1-20 (column A) for all sections. The user will then get additional information that will require them to refine the model. For clarify the attached file goes through single scenario in which the user wants to add 3 new rows below #10 (in column A) for all sections (volume, price, revenue).
In the attached file you'll find three worksheets.
- The Original worksheet shows an example of a model prior to making any changes.
- The Modified (inserted new rows) worksheet shows (in my mind) a step within the code in which the user states they want to insert 3 new rows below #10 (in column A) in all sections within the model (recalling each section has 50 rows and is labeled with 1-50 in column A). So when this step is completed each section will have 53 rows instead of 50.
- The Modified (New Rows Populated) worksheet is trying to show how I need to copy formulas, update column A to have the correct sequenced #’s from 1-53, and then copy the formulas found in columns V-AV for the three newly created rows. Anything highlighted in yellow in my mind has to be updated after the rows are inserted. The red highlight in the Master Section (Volume) will require user input. Once they input the data it will filter into the sections below.
So in summary I’m looking code that looks in column A and finds value = 10 (value specified by the user) in section 1 and then adds a user specified # of new rows below value #10, then it would go to section 2 and find value 10 and add the same amount of user specified rows below value 10 in that section and continue to loop through all sections found in the model to add the new rows to all sections in the same place within each section. Then if possible updated the values in column A so the numbers go from 1-53 or 1-58 if the user says they wanted to add 8 rows instead of 3. Then lastly copy and paste the formulas found in row 10 to the newly created rows below #10.
If there’s a real easy way to complete what I’m asking please just let me know. This can be done manually but is very time consuming and user acceptance of the manual approach is very low. I’ve searched the forum and the net and found code that lets you add a specified row at various points (i.e. every 10th row) but nothing that quite does exactly what I'm trying to do. Then I've also found a basic idea on trying to use sorting to accomplish this task but so far I haven't found a solution that works.
Thanks in advance for any help. Any questions let me know.
Hmm...apparently you can't post attachments. Well hopefully the above details are detailed enough to understand what the model looks like and what I’m trying to do. I’ll dig around a little more to see how I can get my example file more visible to everyone. Any advice on this let me know.
