Excel New Row problem

[erisar1]

I have a spreadsheet in which "employees" are listed on column 1, their "monthly sales budget" in column 2, their "current total sales to date" in column 3, and a formula in column 4 that divides the column 3 cell by the column 2 cell to get their "current % to budget".

I also have a new sheet that has a bar graph that represents all the employees and their "current % to goal".

I want to be able to somehow easily add and remove rows (employees), while keeping the column 4 formula and automatically adding/deleting the row (employee) from the bar graph. I considered just pasting the formula down a few extra times for "future employees" and having the bar graph target the currently filled rows and the "future employees", but this leaves a lot of blank space on the graph, reserved for those "future employees".

If someone could decypher all of that... Please Help!

 

[Hasit]

The best suggestion I have is to create a graph based on a named range. The named range can include as many cells as you wish, so as you add more data, the more detail will be presented on the graph.

To name a range, highlight the cells you want to name and then hit CTRL-F3. Define the name and you can then use this name (call it something unique and not related to cell names such as A1, B3 etc) to reference in graphs etc.

As for ignoring zeros, take a look at this FAQ: faq68-1277

 

[erisar1]

Thanks Hasit - the FAQ helped a lot with a few other problems I was having in this complicated workbook. However I'm still having a problem with the graph...

Lets say I have cells A1:A15 set as my graph data, and cells 11-15 are #N/A (as in FAQ). The bar graph will show bars for all 15 rows. I just want it to show the 10 rows that currently have data, and later when I add data into cells 11-15, for the bar graph to update and include those bars. In other words, until there is content in those #N/A cells, I want the graph to omit them.

Any suggestions??
Thanks in advance!

 

[acron]

What you need is Dynamic named range. Check these two links at Beyond Technology.

http://www.beyondtechnology.com/geeks007.shtml

http://www.beyondtechnology.com/geeks021.shtml

A.C.

 

[erisar1]

Thanks Acron! That was exactly the solution I was hoping for!