Not so obvious Chart?

[rvExcelNewTip]

Is it possible to draw a chart in WorkSheet1, based on data residing in
WorkSheet2?

I tried by Naming (Define Name) the data in Sheet2 and then using these
Names for the Series. But the Charting Wizard always replies with Wrong
Formula!
Entering the ranges (prefixed with the worksheet name) doesn't help
either.

I certainly must be doing something wrong, isn't it?

 

[Andy Pope]

Hi,

Jon Peltier's page on the topic should help.
http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html

Cheers
Andy

 

[Jon Peltier]

There are at least two ways to do this without going through the
problematic technique of typing in the whole address yourself:

1. Start on the sheet with the data, make the chart using the chart
wizard. In step 4 of the wizard, select the desired target sheet in the
As Object In dropdown list.

2. Start on the sheet where you want the chart to reside, and when you
get to step 2 of the wizard, select the other sheet tab with the mouse
(you can switch to another workbook too, using the Window menu), and
select the data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[rvExcelNewTip]

Andy, John: I defined Names local to Sheet2 to reference the Data and
then used these qualified names to modify the Series for the chart in
Sheet1.

The problem I had was finally a tiny (!) programming error, but an
error indeed.

As I was working with dynamic Ranges, I had to Calculate their extents
(with the Address function). Then I added the Named Ranges to the
Names Collection:
... -RefersTo:= strSheet2Name & "!" & Address(rngData)-

Those in the know spot the error immediately: I forgot the leading
equals sign.
... -RefersTo.= "=" & strSheet2Name & "!" & Address(rngData)-

Which indeed resulted in a wrong formula for the Series.

PS. In the meantime I discovered the Name property of a Range which
does the same but with a more elegant syntax.

 

[Tushar Mehta]

You may also want to develop the habit of always sticking in a single
quote around the workbook/sheet name. That ensures your code works
even if the name contains a character such as a space.

ActiveWorkbook.Names.Add Name:="'sheet 1'!aName2", RefersToR1C1:= _
"='Sheet 1'!R5C1:R7C1"

--
Regards,

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

nospam.com>, (e-mail address removed)
says...