Dynamic Charts Offset



Please help. I have read other posts but my situation is a bit different and
I am having trouble adapting the suggestions to my situation.

I am creating a bar chart that shows per contract the estimated hours and
the actual hours. CUrrently, I have contracts that have no estimated and
actual hours, so I do not want to display these contract on the chart, I only
want to show those contracts that have data. I do not want to use auto
filter to exclude those contracts with zero values. I used the suggestions
from the user community and populate zero values with #N/A via a formula when
the value is zero, thinking this would eliminate these from the chart, but it
does not. I tried using OFFSET, but I can't get that to work either because
I have more than one column and I am displaying the contracts in a specific

Here is an example of my data, and since fed from another spreadsheet,
additional estimated and actual values could be populated:

2006 YTD 2006 YTD
Estimated Actual
FTA 234 162
Men 156 412
Mon #N/A #N/A
NYC 453 652

So what I want to see on my bar chart is the 3 contracts that have values on
the x axis, and the corresponding estimated and actuals on the y axis. Then
as additional contracts have values in the estimated and actual columns, they
too will show up on the bar chart. What I see now is
all the contracts, regardless of whether there is a value in the estimated
and actual hours.



Jon Peltier

The #N/A only simulates a non-entry in a line or XY series, but not in a
bar, column, or area chart.

Neither #N/A, zero, nor a true blank cell prevent a point from taking space
in the chart. The chart series includes it as a point, even if it doesn't
"appear". In your case, you will see the #N/A projects as categories in the
chart. What you need to do is use some formulas in a second range that
extract plottable data, and use this second range as the chart source.

For example, if I put your data into A1:C18, I can add a few columns and get
something to plot:

2006 YTD 2006 YTD

Est Actual Est Actual

CIG 0 0 14 NYC 453 652

CIB 0 0 9 Men 156 412

DRS 0 0 6 FTA 234 162

FTA 234 162 0 #REF! #REF! #REF!

IPS 0 0 0 #REF! #REF! #REF!

SIR 0 0 0 #REF! #REF! #REF!

Men 156 412 0 #REF! #REF! #REF!

Mon 0 0 0 #REF! #REF! #REF!

NYCA 0 0 0 #REF! #REF! #REF!

NYCB 0 0 0 #REF! #REF! #REF!

NYCP 0 0 0 #REF! #REF! #REF!

NYC 453 652 0 #REF! #REF! #REF!

NYCW 0 0 0 #REF! #REF! #REF!

NYSC 0 0 0 #REF! #REF! #REF!

NYSP 0 0 0 #REF! #REF! #REF!

NYSP6 0 0 0 #REF! #REF! #REF!

Cell D3 contains this array formula (entered using CTRL+SHIFT+ENTER, not
just ENTER):


Don't type the curly brackets, Excel puts them there if you've correctly
entered the array formula. What it does is find the rows that have non-zero
data (use zeros or blanks in the range, not #N/A). This formula is filled
downwards as far as needed.

Cell E3 has this regular formula:


This is filled right and left as far as needed. It picks out the value for
the row in column D.

You can set up dynamic ranges for columns E:G, and use these in the chart.

- Jon
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions


I get to the point where columns D to G match your sample, but when I create
the bar chart, the #REF! in column E is on the x axis. So what I see is NYC
hours, Men hours and FTA hours, then #REF! 13 more times. How do I set up my
chart to not plot beyond where there are hours?

Jon Peltier

I advised:
You can set up dynamic ranges for columns E:G, and use these in the chart.

but I didn't say how, did I? There are lots of references for dynamic
charts. You can start looking here:


You need to set up some dynamic ranges. On the Insert menu, select Names,
then Define. In the Name box type a name, like 'Labels', and in the Refers
To box enter a formula like

Click Add, then repeat for these additional names:



Start the chart wizard, pick a chart type in step 1, and in step 2 select
the Series tab. Click Add, and in the Category Labels box, type
=Sheet1!Labels (substituting the name of your sheet), then in the Values
box, type =Sheet1!Est. Click Add again, and in the Values box, type
=Sheet1!Actual. Finish the wizard, and admire your chart.

- Jon
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions




Hi Jon

This was a very nice thread for me to read. It solved a problem I ha
perfectly, almost. When doing it interactivly it works perfect, but
need to create it in VBA and that only works up until connecting th
names to the chart.

I tried to record it and then the macro used the charts series.xvalu
and series.value i.e.
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("N28")
ActiveChart.SeriesCollection(1).XValues = "=FileName.xls!Lables"
ActiveChart.SeriesCollection(1).Values = "=FileName.xls!Values"
ActiveChart.SeriesCollection(1).Name = "=""test"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

I did not get this to work so I tried using the formula i.e.

This does not work either. It work if a I put a range like "$A$1:$A$2
instead put that not what I want.

Have you tried this and if so, do you have any proposals on how I coul
solve it?

Thank you in advance

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question