Not so obvious Chart?

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?

Hi,

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

Cheers
Andy

rvExcelNewTip wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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

> 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, 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.

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

In article <rvExcelNewTip.1pqiad_1117271115.0853@excelforum-
nospam.com>, rvExcelNewTip.1pqiad_1117271115.0853...rum-nospam.com
says...
>
> 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.
>
>
> --
> rvExcelNewTip
> ------------------------------------------------------------------------
> rvExcelNewTip's Profile: http://www.excelforum.com/member.php...o&userid=15668
> View this thread: http://www.excelforum.com/showthread...hreadid=373952
>
>