Dynamic Chart formula issue

[Sullie08]

Hi,

I am creating a tracking spreadsheet to monitor fill volumes. The chart can have up to 16 fill heads & up to 30 samples for each head.
I have created a dynamic chart so it will only show the correct number or results however the chart still leaves space for all 32 samples.
If I delete the formulas it works as I intend & the chart only shows the 8 samples, I can add extra samples & the chart will update to include them.
Unfortunately I can't have the final sheet so users can delete the extra samples.
Is there a way to get the chart to not include the formulas that are blank?

I hope I'm making sense in what I'm asking.

[HansV]

Could you attach a stripped down copy of the workbook that demonstrates the problem, without sensitive data?

[Sullie08]

Hi Hans,

I've attached a sample. IN the attached there are 8 samples, I want them to be spread across the chart if possible but at the moment the chart leaves space for all possible samples.

[HansV]

Thanks! the problem is that Excel treats any cell with a formula as non-blank, even if the formula returns an empty string "". So COUNTA counts all cells in C14:AL14.
I changed the definition of the named ranges to use =COUNTIF($C$14:$AL$14, ">0") instead of COUNTA($C$14:$AL$14).

Trend.xlsx

[Sullie08]

Ah that's perfect. Thanks a lot Hans.

[Sullie08]

Hi, I've run into another problem with my sheet. The chart works when there are some results but when there are no results the chart shows all 30+ points.
There are 16 heads but if only 8 heads are used the results for those heads displays correctly but there is a gap on the X axis due to the remaining heads. Is there something I can update to prevent that? If you delete all the results in the example sheet I uploaded you'll see what I mean.
I've tried putting NA(), "" and N/A in the formulas but they still show up.

Looking at historical batches this is possibly not an issue, seems it always uses 16, but if there is a way just for future reference would be good to know.

[HansV]

Can you upload an example demonstrating the problem? Thanks in advance.

[Sullie08]

This sheet has been updated with the formula you gave me. Heads 1-8 display correctly but from 9 when there is no results is messing up the chart.

[HansV]

Wow - that changes the problem completely. In your previous example, there was only one chart series, and we only had to change the number of data points dynamically. In the new one, we have to change the number of chart series dynamically too, depending on N4. That requires VBA code, so the attached workbook is a macro-enabled workbook (.xlsm).

PS 1: The formulas in rows 87 and 92 were different from those in rows 17, 22, ..., 82, causing problems. I made them similar to those above.
PS 2: Shouldn't Data Iput be Data Input?

Trend new.xlsm

[Sullie08]

Thanks Hans, I'll take a look but I'm not sure I can use macros for it. I want the sheet as simple as possible.
Hopefully it isn't needed but I just wanted to know if it could be done.
I had spotted the formula difference myself as well & corrected it. I'm not sure why I had them different, I probably made a change but didn't update all formulas as I was mainly trying to get the chart the way I want it.

For the Iput, yes it should be Input. I have amended a sheet someone else created & that was the tab name. I think they were in a hurry & didn't have time to type the n .

[HansV]

The code runs fully automatically - the user won't have to run it manually.