Help! Dynamic horizontal chart not having dynamic labels whilst i followed all instruction

Hi There,

All my labels are set up but it is stil not working i have followed the instructions of post: excel-charting-and-pivots/655282-dynamic-chart-with-horizontal-table
I have made all my labels. and i have added Two of those labels inside a Graph the WK notation is working dynamicly but the Range VRDLBL1 VRDLBL2 are not working dynamicly

I have changed the COUNTA formula to a COUNT IF >0 because it was counting my formula's as well.

Attached the file hope someone could help me
im overlooking something

The Tab Voorraad is where the graph is.

Sincere greetings.

Aaron

Hi there,

I've changed the formulas in the highlighted cells (rows 1 & 2) of the "Voorraad" worksheet so that #N/A is displayed instead of Zero when no data exist for the week in question - this means that weeks for which no data exist are not included in the graph. Is the graph in the attached version of your workbook what you need?

Hope this helps.

Regards,

Greg M

Originally Posted by Greg M

Hi there,

I've changed the formulas in the highlighted cells (rows 1 & 2) of the "Voorraad" worksheet so that #N/A is displayed instead of Zero when no data exist for the week in question - this means that weeks for which no data exist are not included in the graph. Is the graph in the attached version of your workbook what you need?

Hope this helps.

Regards,

Greg M

Thanks man I will try this at work on Monday and I will reply again ! Funny if this was so simple :p

Hey man,

No i will change the Graph.
But this was what i was looking for!
So this was what i was missing? the need for displaying N/B?

greetings.

Aaron

Hi again,

Yes - a graph will display a zero value, which is what the formula produces in a cell for which no data is available - those cells DO contain zeroes even though the NumberFormat property of those cells is such that a blank value is displayed.

A graph will not display a #N/A value (which is what the new version of the formula produces).

Hope this helps,

Regards,

Greg M

Hi again Greg,

Have you seen that i have made labels that have offset functions and that the graph is pointing to those labels?

=OFFSET(Voorraad!$B$2,0,0,1,MAX(IF(ISNA(Voorraad!$B$2:$BA$2),0,IF(Voorraad!$B$2:$BA$2<>"",COLUMN(Voorraad!$B$2:$BA$2),0)))-1)
=OFFSET(Voorraad!$B$3,0,0,1,MAX(IF(ISNA(Voorraad!$B$3:$BA$3),0,IF(Voorraad!$B$3:$BA$3<>"",COLUMN(Voorraad!$B$3:$BA$3),0)))-1)

and that this label looks at those two?
=OFFSET(Voorraad!$B$1,0,0,1,MAX(COUNTIF(VRDLBL1,">0"),COUNTIF(VRDLBL2,">0")))

i made this because i was following the other post and have downloaded that workbook that the guy made cause i wanted to do the same thing
the N/B is kinda ugly. mind helping me with the label function?

Hey Greg,

This is exactly the solution i was looking for !.
Your formula seems way more cleaned up than my does.
I can now conclude from your formulate that.

The offset is triggered by : if a number is anything bigger or smaller than 0 the range will increase or decrease in size.
the WK label looks at those labels and will increase or decrease in size.

Thanks alot man!