Dynamic Charts

[Spikenaylor]

Hi

trying to setup a dynamic chart

Got it working correctly with empty cells, enter data in the cells and the chart grows or shrinks correctly.

My problem is

Cell B5 is waiting for user data

Cell C5 has a formula =IF(B5<>"",C92,NA()) or =IF(B5<>"",C92,"") so it populates with data from cell C92 when correct data entered into B5

Because the cell has a formula in it the chart is now static and will not shrink or grow.

How can I format cell C5 so it appears blank when no data is entered into B5 so my chart can be dynamic.
Many thanks

Mike

 

[tmmellor1981]

Hi

Normally i'm the one asking for help but think I know how to fix that issue, just add another IF at the beginning to say if B5 blank make C5 Blank

=IF(B5="","",IF(B5<>"",C92,NA()) or =IF(B5<>"",C92,""))

regards
Tim

 

[Spikenaylor]

I worked another solution out by using COUNTIF function to count how many cells have non data in in my defined name range for the data

I leave the C5 cell with the formula =IF(B5<>"",C92,"") in.

My range is B5 to B34 that the users enters data, all the formulas are in C5 to C34

In my defined name range this is the formula =OFFSET('Viability Sheet'!$C$5,0,0,COUNTA('Viability Sheet'!$C$5:$C$34)-COUNTIF('Viability Sheet'!$C$5:$C$34,"*"),1)

Works great, my chart shrinks and grows just perfectly.