Dynamic range for chart

%3CLINGO-SUB%20id%3D%22lingo-sub-1567688%22%20slang%3D%22en-US%22%3EDynamic%20range%20for%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567688%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20two-level%20(two-column)%20range%20to%20populate%20horizontal%20axis%20in%20chart.%26nbsp%3B%20Months%20in%20column%20A%20and%203%20categories%20for%20each%20month%20in%20column%20B.%26nbsp%3B%20I%20want%20to%20be%20able%20to%20make%20the%20two-level%20range%20a%20dynamic%20range%20to%20auto-populate%20the%20chart%20each%20month%2C%20and%20used%20the%20offset%2Fcounta%20combination%20formula%20to%20define%20the%20range%20as%20follows%20(%3DOFFSET('Sheet1'!%24A%248%2C0%2C0%2CMAX(COUNTA('sheet1'!%24A%248%3A%24A%2441)%2CCOUNTA('sheet1'!%24A%248%3A%24B%2441)%2C2))%20since%20there%20are%20blanks%20in%20the%20month%20column.%26nbsp%3B%20The%20trouble%20I%20am%20running%20into%20is%20when%20using%20the%20define%20name%20to%20populate%20the%20horizontal%20axis%20in%20the%20chart%2C%20it%20only%20populates%20the%20months%20and%20omits%20the%203%20categories.%26nbsp%3B%20What%20is%20the%20best%20solution%20to%20fix%20this%20so%20that%20the%20chart%20correctly%20populates%20the%20two-level%20axis%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20converting%20the%20data%20to%20a%20table%2C%20and%20unfortunately%2C%20it%20does%20not%20auto-populate%20the%20chart%20as%20expected%20so%20I%20used%20the%20offset%20formulate%20as%20the%20only%20other%20option.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1567688%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576559%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20for%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576559%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750352%22%20target%3D%22_blank%22%3E%40ctgrs12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20very%20hard%20to%20follow%20what%20is%20the%20problem.%20maybe%20that%20is%20why%20no%20one%20is%20able%20to%20help%3C%2FP%3E%3CP%3EI%20suggest%20you%20upload%20a%20file%20pls%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584835%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20for%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584835%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746148%22%20target%3D%22_blank%22%3E%40ramizassaf%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20reply.%26nbsp%3B%20I%20attached%20a%20sample%20file.%26nbsp%3B%20I%20was%20able%20to%20create%20a%20dynamic%20range%20for%20columns%20C%20and%20D%20using%20the%20formulas%20in%20C2%20and%20D2%2C%20setting%20them%20up%20in%20the%20name%20manager%20and%20using%20those%20as%20the%20data%20for%20the%20chart.%26nbsp%3B%20What%20I%20am%20having%20trouble%20with%20is%20setting%20up%20a%20similar%20dynamic%20range%20(to%20automatically%20update%20the%20chart%20when%20updating%20for%20new%20months)%20for%20the%20horizontal%20axis%20since%20I%20want%20both%20columns%20A%20and%20B%20as%20two-level%20format%20(see%20chart).%26nbsp%3B%20Hope%20that%20is%20more%20clear.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584885%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20range%20for%20chart%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750352%22%20target%3D%22_blank%22%3E%40ctgrs12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20a%20pivot%20table%3C%2FP%3E%3CP%3Eit%20works%20fine%3C%2FP%3E%3CP%3Echeck%20the%20file%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etry%20adding%20a%20new%20month%20with%20data%20and%20check%20if%20the%20charts%20updates%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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!

3 Replies

@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

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.

@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