Charting Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a problem with charting a Dynamic chart.

I goes ok to set up the dynamic function as a range. It also shows the
correct series. The range is as follows:
=OFFSET(Sheet3!$A$1;1;Sheet3!$P$2-1;COUNTA(INDIRECT("C"&Sheet3!$P$2;FALSE));1)

Where P2 is an input from a listbox, and the name of the range is:
Sheet3!Dato.

When I try to change the reference for the series i get the following message:

"Your formula contains an invalid external reference to a worksheet. Verify
the path, workbook, and range name or cell reference are correct, and try
again."

The series function is below.
=SERIES(Sheet3!$D$1;Sheet3!Dato;Sheet3!$D$2:$D$28;1)
I have also tried to include a ' before and after "Sheet 3".
I have alsoe checked that the workbook does not have an external reference.

There is nothing wrong with the series and it works perfectly when I am not
using the range, but uses A1:A100 etc.

I is also worth mentioning that the dynamic range for the Chart label works
just fine.

Anyone who knows what I am doing wrong.
 
It works for me in the sense that I don't get any error. Are you sure
P2 contains a legitimate value?

That said, I should clarify what "in the sense that I don't get any
error" means. The chart contains only a single point rather than all
the values in the range 2:x.

That is because the XL charting module is a lot less flexible
(forgiving?) than the part of XL that deals with formulas in cells.

While in some cases it handles the INDIRECT function just fine, in
other instances it doesn't. In this instance, for some reason, it only
returns a single value from row 2.

What does work is a named formula:
=OFFSET(Sheet1!$A$1,1,Sheet1!$H$1-1,COUNTA(OFFSET(Sheet1!$A:
$A,0,Sheet1!$H$1-1)),1)

My tests involved sheet1 (rather than sheet3) and the column to plot
was controlled by H1 (rather than P2).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top