Help with dynamic data and chart

Hi all! 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

Originally Posted by Andy Pope

You can use this named range for the Category labels

=OFFSET(Sheet1!$A$8,0,0,COUNTA(Sheet1!$B$8:$B$41),2)

It does assume that there are no gaps in the inner layer of labels.

Also there is a table linked chart that appears to be working.

Thank you for your response! I ended up switching the formula to INDEX instead of OFFSET to keep the worksheet from getting slow from all the recalculations (especially with all the charts and formulas to keep them dynamic). For the original question, is there an option using INDEX instead OFFSET?