Step 6: Insert your chart. Create a regular chart for just one series, e.g. North. Then (1) edit the chart source (right-click the chart > select data) and (2) replace the cell ranges in the ‘Series Values’ with (3) your dynamic named range.
I got this far, but I haven't been able to change the series values with the DNR, I keep getting an error message! please help
Hi MrAhmed,
Please upload your Excel file so we can see where the issue is.
Thanks,
Mynda
File attached. thanks
Hi MrAhmed,
Thanks for sharing the file. I can see instantly that the formula is using double quotes that are not the correct format. If you look at them closely you'll see they are italicised and Excel doesn't recognise them as double quotes. This often happens when you copy a formula from a web page and the web page font is different to that used by Excel formulas.
If you type a double quote into your formula you'll see the difference.
Here is the correct format:
=IF('Top20'!$D$21="AMCO",AMCO, IF('Top20'!$D$21="ASHTEAD",ASHTEAD, IF('Top20'!$D$21="BALFOUR", BALFOUR, IF('Top20'!$D$21="BMM",BMM, IF('Top20'!$D$21="CAP",CAP, IF('Top20'!$D$21="CH2M",CH2M, IF('Top20'!$D$21="GBV",GBV, IF('Top20'!$D$21="HALCROW",HALCROW, IF('Top20'!$D$21="HITACHI",HITACHI, IF('Top20'!$D$21="INTERSERVE",INTERSERVE, IF('Top20'!$D$21="JACKSON",JACKSON, IF('Top20'!$D$21="JACKSONHYDER",JACKSONHYDER, IF('Top20'!$D$21="JBA",JBA, IF('Top20'!$D$21="LA",LA, IF('Top20'!$D$21="MAVEN",MAVEN, IF('Top20'!$D$21="MOTT",MOTT, IF('Top20'!$D$21="Redfern",REDFERN, IF('Top20'!$D$21="SSCL",SSCL, IF('Top20'!$D$21="TVO",TVO, IF('Top20'!$D$21="VBA",VBA, IF('Top20'!$D$21="TOTAL",TOTAL)))))))))))))))))))))
Mynda
Thank you so much! Your MVP skills know no bounds! 
