Creating 1 chart with 2 sources

[Maureen]

I would like to create a chart based on the following
scenarios:

1. If cell A1=B1, then the chart source is AA10:BB14
OR
2. if cell A1 is not equal to B1, then the chart source
would be AA20:BB30

Is there a way where this could be easily done? Any help
is greatly appreciated. Thank you!

 

[Tushar Mehta]

[This followup was posted to microsoft.public.excel.charting with an
email copy to Maureen.
Please use the newsgroup for further discussion.]

You can do this easily with named formulas.

Using AA:AC as an example (rather than AA:BB, which is 28 columns!):

Create the named formulas below with Insert Name > Define...

XVals
=IF(Sheet1!$A$1=Sheet1!$B$1,Sheet1!$AA$10:$AA$14,Sheet1!$AA$20:$AA$30)
Y01Vals =OFFSET(XVals,0,1)
Y02Vals =OFFSET(XVals,0,2)

You will need to create an additional name for each set of Y values.

Now, create a chart with these names as the series. For how, see the
'Dynamic Charts' tutorial of my web site.

--
Trouble finding replies to your posts? Use a newsreader. See the
tutorial 'Outlook Express and Newsgroups' on my web site

Regards,

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