Solved

Dynamically assign  range names in a worksheet

Posted on 2016-07-22
5
52 Views
Last Modified: 2016-07-23
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
0
Comment
Question by:donpick
5 Comments
 
LVL 17

Assisted Solution

by:Roy_Cox
Roy_Cox earned 125 total points
ID: 41725555
Your data should be in table format with one header row and no completely empty rows or columns of data. Use the SUBTOTAL Formula then you can Filter the data by Company and get individual Totals, SUBTOTAL will only sum the visible rows.

Which version of Excel are you using? If you are using one of the newer versions of Excel I would format the data as a Table
ask-excel-question.xls
0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 125 total points
ID: 41725556
I would consider combining all the trade data together and then use Excel subtotals tool to produce the total amounts. I don't have a computer with Excel 2000 (oldest I have is 2003), but this should work.

You'll remove all the blank rows and totals, then sort by "Trade" (stock ticker) and "Date".  Then select "Data" and then "Subtotals..." from the menu.  Then, in the new dialog, select "Trade" in the list "At each change in:".  Then select "Sum" in the "Use function" list. Then click the check boxes for "Sub totals" and "Profit".  Click the [OK] button.  You can use this same sequence to remove subtotals at any time.

Until you get a handle on this, I recommend making a copy of your data sheet and apply the subtotals to that copy.

See also this YouTube video for creating subtotals in Excel 2000
https://www.youtube.com/watch?v=rmZuE4Sa2oY

The other advantage of combining all you data is that you can then use PivotTables to achieve the same summary of the data.

Regards,
-Glenn
EE-ask-excel-question.xls
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 250 total points
ID: 41725557
Instead of having the current data layout, convert your data range into an Excel Table and dump your data data for each date and trade without having any blank rows in between.
Then on another sheet, you can visualize the data as per your requirement with the help of a Pivot Table.
The advantage of an Excel Table is, the table range is self expandable once you add more data down the table and after adding the new data, right click the Pivot Table and choose refresh to reflect the new data.
That should be the easiest way considering the nature of your data.

In the attached, you will find two sheets...

1) New Data Sheet: Here you will find the Excel Table having your data.
2) Pivot: Here you can visualize your data and you can adjust it as per your requirement at any time.

See if that helps.
New-Layout.xls
0
 

Author Closing Comment

by:donpick
ID: 41726046
Thank you all for your prompt reply.  I pay for this service so I appreciate detailed answers.  Thank you, Subodah, for the links.  They are very helpful.  I never thought of the ideas you all have provided.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41726168
You're welcome donpick! Glad we could help.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
This Micro Tutorial will give you basic overview of the control panel section on Windows 7. It will depth in Network and Internet, Hardware and Sound, etc. This will be demonstrated using Windows 7 operating system.
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now