Thread: 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
order.

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
CIG #N/A #N/A
CIB #N/A #N/A
DRS #N/A #N/A
FTA 234 162
IPS #N/A #N/A
SIR #N/A #N/A
Men 156 412
Mon #N/A #N/A
NYCA #N/A #N/A
NYCB #N/A #N/A
NYCP #N/A #N/A
NYC 453 652
NYCW #N/A #N/A
NYSC #N/A #N/A
NYSP #N/A #N/A
NYSP6 #N/A #N/A

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.

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):



{=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)>0)*(($C$3:$C$18)>0),ROW()-ROW($D$2))}



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:



=OFFSET(A$1,$D3-1,0)



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
http://PeltierTech.com/
_______





"CSK" <CSK@discussions.microsoft.com> wrote in message
news:506147ED-725F-404E-8E13-0EB705204992@microsoft.com...
> 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
> order.
>
> 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
> CIG #N/A #N/A
> CIB #N/A #N/A
> DRS #N/A #N/A
> FTA 234 162
> IPS #N/A #N/A
> SIR #N/A #N/A
> Men 156 412
> Mon #N/A #N/A
> NYCA #N/A #N/A
> NYCB #N/A #N/A
> NYCP #N/A #N/A
> NYC 453 652
> NYCW #N/A #N/A
> NYSC #N/A #N/A
> NYSP #N/A #N/A
> NYSP6 #N/A #N/A
>
> 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.
>

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" wrote:

> 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):
>
>
>
> {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)>0)*(($C$3:$C$18)>0),ROW()-ROW($D$2))}
>
>
>
> 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:
>
>
>
> =OFFSET(A$1,$D3-1,0)
>
>
>
> 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
> http://PeltierTech.com/
> _______
>
>
>
>
>
> "CSK" <CSK@discussions.microsoft.com> wrote in message
> news:506147ED-725F-404E-8E13-0EB705204992@microsoft.com...
> > 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
> > order.
> >
> > 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
> > CIG #N/A #N/A
> > CIB #N/A #N/A
> > DRS #N/A #N/A
> > FTA 234 162
> > IPS #N/A #N/A
> > SIR #N/A #N/A
> > Men 156 412
> > Mon #N/A #N/A
> > NYCA #N/A #N/A
> > NYCB #N/A #N/A
> > NYCP #N/A #N/A
> > NYC 453 652
> > NYCW #N/A #N/A
> > NYSC #N/A #N/A
> > NYSP #N/A #N/A
> > NYSP6 #N/A #N/A
> >
> > 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.
> >
>
>
>

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:

http://peltiertech.com/Excel/Charts/Dynamics.html

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
=OFFSET(E3,0,0,COUNTIF(D:D,">0"),1)

Click Add, then repeat for these additional names:

'Est'
=OFFSET(Labels,0,1)

'Actual'
=OFFSET(Labels,0,2)

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
http://PeltierTech.com/
_______

"CSK" <CSK@discussions.microsoft.com> wrote in message
news:D1BDFFCE-619D-4FCC-AD48-DE785CF3F7B0@microsoft.com...
>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" wrote:
>
>> 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):
>>
>>
>>
>> {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)>0)*(($C$3:$C$18)>0),ROW()-ROW($D$2))}
>>
>>
>>
>> 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:
>>
>>
>>
>> =OFFSET(A$1,$D3-1,0)
>>
>>
>>
>> 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
>> http://PeltierTech.com/
>> _______
>>
>>
>>
>>
>>
>> "CSK" <CSK@discussions.microsoft.com> wrote in message
>> news:506147ED-725F-404E-8E13-0EB705204992@microsoft.com...
>> > 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
>> > order.
>> >
>> > 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
>> > CIG #N/A #N/A
>> > CIB #N/A #N/A
>> > DRS #N/A #N/A
>> > FTA 234 162
>> > IPS #N/A #N/A
>> > SIR #N/A #N/A
>> > Men 156 412
>> > Mon #N/A #N/A
>> > NYCA #N/A #N/A
>> > NYCB #N/A #N/A
>> > NYCP #N/A #N/A
>> > NYC 453 652
>> > NYCW #N/A #N/A
>> > NYSC #N/A #N/A
>> > NYSP #N/A #N/A
>> > NYSP6 #N/A #N/A
>> >
>> > 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.
>> >
>>
>>
>>

Hi Jon

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

I tried to record it and then the macro used the charts series.xvalue and series.value i.e.
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("N28")
ActiveChart.SeriesCollection.NewSeries
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.
xlChartSeries.Formula = "=SERIES(""test"",FileName.xls!Lables,FileName.xls!Values,1)"

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 could solve it?

Thank you in advance
Anders.

Originally Posted by 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:

http://peltiertech.com/Excel/Charts/Dynamics.html

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
=OFFSET(E3,0,0,COUNTIF(D:D,">0"),1)

Click Add, then repeat for these additional names:

'Est'
=OFFSET(Labels,0,1)

'Actual'
=OFFSET(Labels,0,2)

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
http://PeltierTech.com/
_______

"CSK" <CSK@discussions.microsoft.com> wrote in message
news:D1BDFFCE-619D-4FCC-AD48-DE785CF3F7B0@microsoft.com...
>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" wrote:
>
>> 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):
>>
>>
>>
>> {=LARGE(ROW($A$3:$A$18)*(($B$3:$B$18)>0)*(($C$3:$C$18)>0),ROW()-ROW($D$2))}
>>
>>
>>
>> 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:
>>
>>
>>
>> =OFFSET(A$1,$D3-1,0)
>>
>>
>>
>> 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
>> http://PeltierTech.com/
>> _______
>>
>>
>>
>>
>>
>> "CSK" <CSK@discussions.microsoft.com> wrote in message
>> news:506147ED-725F-404E-8E13-0EB705204992@microsoft.com...
>> > 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
>> > order.
>> >
>> > 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
>> > CIG #N/A #N/A
>> > CIB #N/A #N/A
>> > DRS #N/A #N/A
>> > FTA 234 162
>> > IPS #N/A #N/A
>> > SIR #N/A #N/A
>> > Men 156 412
>> > Mon #N/A #N/A
>> > NYCA #N/A #N/A
>> > NYCB #N/A #N/A
>> > NYCP #N/A #N/A
>> > NYC 453 652
>> > NYCW #N/A #N/A
>> > NYSC #N/A #N/A
>> > NYSP #N/A #N/A
>> > NYSP6 #N/A #N/A
>> >
>> > 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.
>> >
>>
>>
>>