Forum Discussion

ctgrs12's avatar
ctgrs12
Copper Contributor
Aug 05, 2020

Dynamic range for chart

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!

Charting
excel
office 365
training

3 Replies

  • Ramiz_Assaf's avatar
    Ramiz_Assaf
    Iron Contributor
    Aug 09, 2020

    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

    • ctgrs12's avatar
      ctgrs12
      Copper Contributor
      Aug 12, 2020

      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.

Resources