Forum Discussion
Chart Data Range grows as I insert new rows at the top
I have a table and two graphs overlaid. I want the chart data range to remain fixed from row D2-D40. I am using this range, that I got from somewhere on the Internet, which I don't understand it very well:
='Q 1'!$D$1:$D$40,'Q 1'!$J$1:$J$40
It works, but the bottom of the range expands as I insert new rows near the top. How can I get the graph to not expand beyond D40. I also do not understand why, or if 'Q1" even needs to be there, or why it starts with D1 (the header). I will be replicating the Q1 sheet to subsequent sheets for the other quarters of the year.
The other graph is configured similarly on a different column.
I insert at the top, because I add several new entries with the most current at the top each day, to avoid sorting after each entry.
11 Replies
wcstarks The 'Q 1!' bit merely refers to the sheet name of the range referenced after it. If you are referencing cells in the same sheet where the formula is entered, you can just delete it.
But perhaps you can upload an example of your workbook (replace any confidential information though) so that someone out here can have a closer look. It seems you want the graph to only include the first 40 rows of data in your table. Correct?
1) Yes, I want both graphs to only include an arbitrary last 40 entries. I add two to three entries a day. After awhile, the graphs would become so compressed that they would not be very useful.
2) The graphs will also not stay fixed in their position on the first page, even though they and the grouping object are all set to have them not move. They do anyway. I only print the 1st page as needed and the graphs are suppose to print at the bottom of the 1st page.
3) The new inserted rows will also not pick up all the cell formatting, such as the vertical Centering, even though they do pick up the horizontal centering and all the formulas and the one drop down list.
Since I am including the workbook, I decided to include references to 3 issues I have been unable to resolve. I have attached the excel workbook. Any help I can get on these issues would be appreciated.
wcstarks I believe I fixed the Q1 charts for you (see attached). The trick is to create dynamic Named Ranges using the OFFSET function, for all four series in a quarter. They take the first cell below the relevant header as the starting point plus the next 40 rows (by 1 column wide.)
Created a named range called "Q1Glucose" =OFFSET(tblQ1[[#Headers],[Glucose]],1,,40,1)By the way, I renamed your Table1 to tblQ1 for clarity.
Then, you enter
='Q 1'!Q1Glucosefor the Y values, rather than ='Q 1'!$D$2:$D$41
Once you have saved the workbook, you'll notice that the references will have changed to
='2021 Linda Tracking (ADR).xlsx'!Q1GlucoseThe grouped charts stay in place on my computer, so I can't replicate the problem.
Selected the entire table, set it to center cells vertically and can insert rows, keeping that format.
I trust you can repeat the process for the coming quarters yourself. If not, let me know.
