Dynamic Chart Range - Stumped!

Are you still using Excel 2013? If not, then please update your profile.

The best way to deal with charts in my experience is to create a dynamic table that pulls a summary from the actual data and to use that as the chart source. I'll have a look and see if that might work for you.

Great!

Move the chart aside, then in B11:

=LET(n,FILTER(C4:C7,B4:B7="YES"),f,FILTER(D4:G7,B4:B7="YES"),HSTACK(VSTACK(C3,n),FILTER(VSTACK(D3:G3,f),D2:G2="YES")))

Select data for the chart: C11:G15
Select data for horizontal data labels: D11:G11

Now move the chart back over the dynamic table.

Adjust YES and NO selections to check.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

That's annoying - sorry about that.

I can't help you with OFFSET, but I'll have another look and see if it can be achieved any other way.

Not getting very far, I'm afraid, Hopefully someone else will be able to crack it for you.

Hopefully - I've put out a call for help.

Try it now...

Is this what you expected?

No, that's showing the same issues, Glenn. Change F2 and B6 to NO and you'll see that the series numbers at the bottom of the chart change and are incorrect. And the first BLUE bar on the chart should not be showing.

I posted your file... not the one I twiddled with. Where did I put it???

Dunno - I was wondering what you'd actually changed!

I found it...

That appears to work - what did you change?

Aha... a secret... I changed the Risk level Names to TEXT by adding an '

Ah! That makes sense.

OK - so it looks like we can do it this way intsead of changing the numbers in the original table:

=LET(n,FILTER(C4:C7,B4:B7="YES"),f,FILTER(D4:G7,B4:B7="YES"),HSTACK(VSTACK(C3,n),FILTER(VSTACK(TEXT(D3:G3,"0"),f),D2:G2="YES")))

Cheers, Glenn!

Final version: summary table (and hence chart) set to show percentages.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Right-click the chart - Select Data.

Because it knows it's working with a dynamic range. Is there a problem?

I don't undresyand the issue. Attach a workbook that shows the problem. It's all working fine for me.

Trying to work out what you are struggling with: the dynamic range is the table we create with the formula. Do you have that selected in Select Data?

Can we assume that you resolved the issue?

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.