Dynamic range for chart

[ctgrs12]

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!

[#2]

@ctgrs12 

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

[#3]

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.

[#4]

@ctgrs12 

I used a pivot table

it works fine

check the file

try adding a new month with data and check if the charts updates