Chart Data Range grows as I insert new rows at the top

Iron Contributor

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?

@Riny_van_Eekelen 

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'!Q1Glucose

for 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'!Q1Glucose

The 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.

@Riny_van_Eekelen 

Thanks for your reply. 

1) When I attempted to open your version of the WB, Excel reported an error.  See attachment below.  I responded with "No", and Excel quit.  I then tried to open my current WB, and got the same error.  I then responded with "yes" and Excel completed opening the WB.  What is that all about?  Did attempting to open your version of the WB cause Excel to behave differently with my current WB?  I will await your response before I follow through with this file.

 

2) I am curious as to why it is necessary to refer to the sheet name when creating these references?  I was thinking that I could eventually just copy Q1 to Q2, Q3 and Q4, empty of data, and not have to make any changes.  Is that not possible?

 

3) Capture 2 is from my current version of the WB. Every time I create or modify a formula, or add new rows, Excel reports this error (green triangle). The options to resolve it do not seem appropriate to fixing whatever is the problem.  So, I end up highlighting the affected cells and telling Excel to ignore.  Why does Excel not like my formulas, even though they seem to work OK?

 

I am more familiar with Access.  Excel is relatively new to me, as you can tell.

 

 

@Riny_van_Eekelen 

Sorry.  I forgot to ask about creating the dynamic named range.  Where do I put the expression you created to create the dynamic named range?

@wcstarks 

Never mind.  I queried online and found I need to use the Name Manager under Formulas.  Sorry for the trouble.

@wcstarks 

On 1) I have no idea. Never heard of that add-in. Nothing from my side.

On 2) That's the way it is. With regard to the named ranges, Excel need to look where to look (like sheet and table name), but perhaps I'm not making it clear enough. Otherwise, create separate files for each quarter. Or just put the entire year's reading in one table.

On 3) Through the green triangles alerts you something might be wrong. Never looked at these formulae before and have Error checking switched off on my system, because I think it's very irritating. So, I never got the green triangles. If you are certain that the formulae are correct, ignore the triangles or just switch off Error checking.

 

Can understand your feelings. I'd probably feel the same when forced to using Access.

 

@Riny_van_Eekelen 

2) Concerning the name ranges, I see that the range scope can be set for the WB, or for just for a sheet.  If I were to change the scope to the sheet, could not those ranges work without the sheet references?  Then, when I copy the Q1 to create a new Q2, etc., would those references not work without modifications? Q2-Q4 all need to be replaced completely, as they are not up to date with the formatting and formulas from the current Q1.

 

1) Once I said yes to that error, I was able to work with the WB normally.  Yesterday, I had to restore my computer to a set point for 20 Feb, because I ended up accidentally installing an AdWare.  Perhaps the issue was related with that restore, and Excel was able to recover from it.  It doesn't seem to be a continuing issue.  I went ahead and replace my version with yours and updated the data for yesterday from my version.  It seems that in your version, the graphs do indeed remain fixed.

 

A) If I could test your indulgence further, I would like to ask a question about a complex If statement involving redundant elements. In the Nutrition table, I have the following formula in column M to calculate the net carbs, based on data in columns G-L.  The redundant elements are designated in bold:

=IF(G2-H2-K-(I2*0.5)-J22 -(L2*0.5)< 1, G2-H2-(I2*0.5)-J2-K2-(L2*0.5), CEILING(G2-H2-(I2*0.5)-J2-K2-(L2*0.5), 0.5))

 

I suspect there may be a way to create a variable within the expression, but if there is, I don't know how to do it.  I have been unsuccessful querying online for the answer.

 

@wcstarks 

On 2) I'm on a Mac right now (not near a Windows PC). Mac Excel doesn't give me the opportunity to check this as it doesn't have the same functionality. But try for yourself and see if it works.

 

On 1) Good!

 

On 3) Using "variables" in formulae is something that was introduced in Excel very recently with the LET function. See if you have it in your version. Otherwise you can put your formula in the Name Manager and make it into a Named Formula.

@Riny_van_Eekelen 

Thanks.  Creating a variable in Name Manager works great!

NetCalc = (G2-H2-(I2*0.5)-J2-K2 -(L2*0.5))

I originally had the scope for the whole WB.  I found that it repeated the name of the table for each cell, making it very verbose.  Since one cannot change the scope of an existing name object, I re-did the variable with the scope of table, thinking it would not need to use the table name for each cell, but that turned out to not be the case.  Excel still used the table name in the expression.

Then, the IF statement, which is much easier to read and write:

=IF(NetCalc < 1, NetCalc, CEILING(NetCalc, 0.5))

 

Although I have a current Excel application, I doubt I have the Beta LET() function.  After looking at the function in context of an If statement, I think creating a variable in Name Manger is simpler to understand, though, perhaps the LET() function would avoid the need to define the scope.  Can you see an advantage of using the LET() function over a variable?

 

Chart name ranges: I looked at scoping them to the sheet/table, but one still needs to name the table. One cannot simply infer the current table.  So, I am not sure that it will help any, unless the table name can be assigned to a variable by/in each Q table.  Does that make sense?  I am not sure how that could even be implemented, even if it were feasible.

 

@wcstarks Glad you worked out how to use the Name Manager to create your own variables. With only one or two repeating calculations in a formula, I guess named formulae work just fine. But if you would work with more, it could easily become "crowded" and hard to keep track of all the defined names. LET, on the other hand, allows you to declare the variables inside the formula. Such formulae, even when getting quite long, are easy to read and maintain. But that's just my personal opinion.

Regarding charts, you can build them anywhere within one workbook or another. So, I guess you need to point each chart to its source data, where-ever that may be. Wouldn't how Excel could figure out what "the current table" is when the chart may not necessarily sit in the same sheet as the table. But, then again, I'm far from knowledgable on the underlying technology. Sorry.