Forum Discussion
Chart Data Range grows as I insert new rows at the top
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.
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?
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.
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.
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.
