donpick
asked on
Dynamically assign range names in a worksheet
Running Excel 2000 on a Windows 7 pro computer.
I don't want to have to create a separate worksheet for each stock I trade. I am struggling with the total cell in row 6 and row 13. (see attached spreadsheet ).
I am NOT a spreadsheet expert. I have defined cell E6 as =sum(E2:E3). If I add more lines below row 3 then cell E6 is not going to show the correct sum.
My understanding is if, in cell E6, I define a range of cells starting with E2 and ending with E5 then if a row is entered above row 5 then the total will show an accurate total. If cell E6 has a range name of Rang1 then the formula to sum any cells above E6 could be =sum(Rang1).
Cells E2,E3 and I2 and I3 have a formula in them which multiplies the quantity times the price. I create rows near the bottom of the worksheet which have all the formatting in it (for dates, etc). Let's call this the “template rows”. The “template” includes the total row (see rows 6 and 13 as examples ). Then, whenever I want to add a new trade, I just copy the the template rows from the bottom, insert them below existing trades enter the data and the subtotals and total are calculated. I don't have to keep entering formulas to calculate subtotals and totals.
My question: I am struggling with the range name. When I copy the template rows, the total row has the same range name as all the other total rows. So the totals in the total rows are not correct. If cell E6 and E13 have the same range name the totals will not be correct. Somehow I'd like to dynamically assign a range name so the totals of each set of trade rows is correct. In other words, the total for IBM might have a range name of Rang1, for JNJ the range name is Rang2. I don't want to have to manually change the range name each time I copy the template rows.
How would you design this spreadsheet to avoid having to manually assign range names?
Or may be my design is flawed and there is a better way to do this?
Please give me a detailed answer. Any useful links are always appreciated.
ask-excel-question.xls
I don't want to have to create a separate worksheet for each stock I trade. I am struggling with the total cell in row 6 and row 13. (see attached spreadsheet ).
I am NOT a spreadsheet expert. I have defined cell E6 as =sum(E2:E3). If I add more lines below row 3 then cell E6 is not going to show the correct sum.
My understanding is if, in cell E6, I define a range of cells starting with E2 and ending with E5 then if a row is entered above row 5 then the total will show an accurate total. If cell E6 has a range name of Rang1 then the formula to sum any cells above E6 could be =sum(Rang1).
Cells E2,E3 and I2 and I3 have a formula in them which multiplies the quantity times the price. I create rows near the bottom of the worksheet which have all the formatting in it (for dates, etc). Let's call this the “template rows”. The “template” includes the total row (see rows 6 and 13 as examples ). Then, whenever I want to add a new trade, I just copy the the template rows from the bottom, insert them below existing trades enter the data and the subtotals and total are calculated. I don't have to keep entering formulas to calculate subtotals and totals.
My question: I am struggling with the range name. When I copy the template rows, the total row has the same range name as all the other total rows. So the totals in the total rows are not correct. If cell E6 and E13 have the same range name the totals will not be correct. Somehow I'd like to dynamically assign a range name so the totals of each set of trade rows is correct. In other words, the total for IBM might have a range name of Rang1, for JNJ the range name is Rang2. I don't want to have to manually change the range name each time I copy the template rows.
How would you design this spreadsheet to avoid having to manually assign range names?
Or may be my design is flawed and there is a better way to do this?
Please give me a detailed answer. Any useful links are always appreciated.
ask-excel-question.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You're welcome donpick! Glad we could help.
ASKER