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