Create a chart that automatically removes '0' value or blank rows from a range

[nicholashoulihan]

Hi - thank you for taking the time to look.

Hoping can please help.

I'm making a document / master template that that can be used for anywhere between 1 and 100 sites. I'm trying to set this up, so all formulas and charts etc will work whether I use this for 1x location or the 100x.

I'd like to have a chart that looks at the range of a 100x sites, however, if it only finds 10x sites have been listed, then it will automatically remove the blank columns from the chart (rather than showing as '0' values). To further complicate things (!), the formula in one of the columns will mean the row isn't truly blank as will contain the formula, but that will always return a '0' / blank unless a site name has been entered...

Hopefully the example I've made in the attached makes it clear what I'm trying to achieve.

Thank you for any help and guidance you can give, BR, Nick

[TMS]

Try:

C5: =SORT(UNIQUE(FILTER(Summary!B3:B100,Summary!B3:B100<>"")))
D5: =SUMIF(Summary!B:B,Charts!C5#,Summary!C:C)

DNR ChartNames =Charts!$C$5:INDEX(Charts!$C:$C, MATCH(REPT("z",9),Charts!$C:$C,1))
DNR ChartData =Charts!$D$5:INDEX(Charts!$D:$D, MATCH(REPT("z",9),Charts!$C:$C,1))

Series ='Excel - Test Case.xlsx'!ChartNames
Series ='Excel - Test Case.xlsx'!ChartData

[TMS]

Is this resolved yet?