Aug 05 2020 09:03 AM
I have a two-level (two-column) range to populate horizontal axis in chart. Months in column A and 3 categories for each month in column B. I want to be able to make the two-level range a dynamic range to auto-populate the chart each month, and used the offset/counta combination formula to define the range as follows (=OFFSET('Sheet1'!$A$8,0,0,MAX(COUNTA('sheet1'!$A$8:$A$41),COUNTA('sheet1'!$A$8:$B$41),2)) since there are blanks in the month column. The trouble I am running into is when using the define name to populate the horizontal axis in the chart, it only populates the months and omits the 3 categories. What is the best solution to fix this so that the chart correctly populates the two-level axis?
I tried converting the data to a table, and unfortunately, it does not auto-populate the chart as expected so I used the offset formulate as the only other option.
Thanks in advance!
Aug 09 2020 10:43 AM
It is very hard to follow what is the problem. maybe that is why no one is able to help
I suggest you upload a file pls
Aug 12 2020 09:34 AM
Hi @Ramiz_Assaf
Thanks for your reply. I attached a sample file. I was able to create a dynamic range for columns C and D using the formulas in C2 and D2, setting them up in the name manager and using those as the data for the chart. What I am having trouble with is setting up a similar dynamic range (to automatically update the chart when updating for new months) for the horizontal axis since I want both columns A and B as two-level format (see chart). Hope that is more clear.
Aug 12 2020 10:02 AM
I used a pivot table
it works fine
check the file
try adding a new month with data and check if the charts updates