Dynamic Chart formula issue

Sullie08
StarLounger
Posts: 59
Joined: 30 Jun 2017, 09:43

Dynamic Chart formula issue

Post by 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.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic Chart formula issue

Post by HansV »

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

Sullie08
StarLounger
Posts: 59
Joined: 30 Jun 2017, 09:43

Re: Dynamic Chart formula issue

Post by 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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic Chart formula issue

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Sullie08
StarLounger
Posts: 59
Joined: 30 Jun 2017, 09:43

Re: Dynamic Chart formula issue

Post by Sullie08 »

Ah that's perfect. Thanks a lot Hans.

Sullie08
StarLounger
Posts: 59
Joined: 30 Jun 2017, 09:43

Re: Dynamic Chart formula issue

Post by 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.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic Chart formula issue

Post by HansV »

Can you upload an example demonstrating the problem? Thanks in advance.
Best wishes,
Hans

Sullie08
StarLounger
Posts: 59
Joined: 30 Jun 2017, 09:43

Re: Dynamic Chart formula issue

Post by 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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic Chart formula issue

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Sullie08
StarLounger
Posts: 59
Joined: 30 Jun 2017, 09:43

Re: Dynamic Chart formula issue

Post by 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 :laugh: .

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Dynamic Chart formula issue

Post by HansV »

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