Bar Chart Pulling Blank Cells

[Fister]

Hello Everyone and thank you in advance for your help -

I have two tabs in an Excel 2007 file. One tab has information being entered into columns like Advertising Campaign Name, How it's being released (ex. TV, Radio, Internet), the date work on the campaign must start, the drop date of the campaign, and a formula calculating the number of days between the start and drop dates (among many, many other things).

The data for campaign name, how it's being released, the date work must start, and number of days between start/drop dates onto the second tab to create a stacked bar chart. The issue is that I'd like to make the chart dynamic but if use one of the following formulas

=IF('Sheet1!B5="","",'Sheet'!B5)
=IF('Sheet1!B5=""," ",'Sheet'!B5)
=IF('Sheet1!B5="",NA(),'Sheet'!B5)

either the blanks or N/A's will appear in the chart.

Any suggestions on keeping the chart dynamic without it grabbing the blank or N/A's ?

[benishiryo]

hi Fister, do u have any way to arrange them so that those with blanks will be at the bottom of your list? then u can use the OFFSET formula & COUNT to make it dynamic. it's hard to point u in a specific direction without a sample file

[Fister]

Sample file attached.

Thank you for your help.

[benishiryo]

i see that u already have some OFFSET formulas in your Name Manager. instead of COUNTA where it counts even formulas contained in the cells, use COUNT to count numbers in Column C. Put all these OFFSET formulas in the Name Manager.
For Campaign Name & Channel:
=OFFSET($A$1,1,0,COUNT($C$2:$C$82),2)

For Initiative Drop Date:
=OFFSET($C$1,1,0,COUNT($C$2:$C$82))

For Days:
=OFFSET($D$1,1,0,COUNT($C$2:$C$82))

Right-click your chart & Select Data. u will have 3 items to edit. 2 items in the Legend Entries & 1 in the Horizontal Axis Labels. in the former, choose Initiative Drop first & click Edit. In the Series Values, delete everything after the "!" & replace it with your OFFSET formula Name for Initiative Drop. Do the same for Days & the Horizontal Axis Labels.

By the way, dealing with days, i would feel the Gantt Chart suits this better. just my humble opinion.
http://chandoo.org/wp/2009/06/16/gan...ct-management/

[Fister]

benishiryo, thank you for your help but there is one issue - the chart does not show all of the campaigns.
In other words, if I have 10 campaigns, only 8 are on the chart.
Is that a formula/space/size/something else issue?

[benishiryo]

hmmm is this problem from the sample u uploaded? i see only 8 in your file & if i add 2 more, they do show in the chart. maybe u gotta upload your most recent one with the OFFSET formulas. i cant tell from here.

[Fister]

I followed your directions - except I changed the Campaign formula to =OFFSET(Chart!$A$2,0,0,COUNT(Chart!$C$2:$C$82),2)
so that the headings do not appear in the chart.

See attached Sample B.


Thank you again for being so generous with your time and help, I very much appreciate it.

[benishiryo]

what u did for Campaign, u need to do the same for Initiative Drop Date & Days too.
same thing. right-click chart & choose Select Data. Edit the one for Initiative Drop.
=Chart!Initiative_Drop_Date
and for Days:
=Chart!Days

[Fister]

I did do the same thing but apparently it didn't take (or I didn't save it correctly).
Thank you very much, you've been a great help!