1 - You asked me to use the Insert Names Define function. Should I
Define the entire column all at once with the OFFSET formulas you listed,
just the top cell in each column, or each cell individually? If
individually, is there a unique name I should give each cell?
Reading ahead, I see I can simplify. I hope I didn't overcomplicate matters.
You only need the following names:
Name Refers To
MyTitle =OFFSET($A$1,$F$1,0)
MyData =OFFSET($B$1:$E$1,$F$1,0)
Each name is its own definition, and it refers to whichever cell is offest
from the top row by the number in cell F1. All the cells don't need names.
Just do Insert Names Define, enter the first name (MyTitle) into the
Name entrybox and the first refers ro formula (=OFFSET($A$1,$F$1,0)) into
the Refers To entrybox. Click Add, then repeat for each of the others in the
list.
I meant to say, when you create your chart, add a title, then select the
title (but don't edit it, there should be no cursor in it), click in the
formula bar, and type
=Sheet1!MyTitle
If your sheet name has a hyphen or space, enclose the name in single quotes:
='Sheet One'!MyTitle
This links the chart title to the appropriate cell in column A.
2 - When I redefine the series (which I'm doing after creating and
customizing the charts by using the Source Data option and replacing the
"Value" field) from "=Sheet1!$B$2:$E$2" to
"=SERIES(Sheet1!$B$1,,Sheet1!MyMin,1)" (minus the quotes, of course) I get
a
"Formula contains an error" message. I must be missing something here.
I explained how to edit the formula bar, above the worksheet column headers.
Change this formula:
=SERIES(Sheet1!$A$1,Sheet1!$B$1:$E$1,Sheet1!$B$2:$ E$2,1)
to this:
=SERIES(Sheet1!MyTitle,Sheet1!$B$1:$E$1,Sheet1!MyD ata,1)
3 - I'm not real sure what you meant by "change the other cell references
to
the names created above." Could you clarify?
I thought it was more complicated than it was. Sorry.
As a side note, I've used your Excel to PowerPoint macros before with
AMAZING results. I am concerned though because the macro I used only
works
on existing charts and I know my computer does not have enough memory to
allow Excel to create 350 or so charts in one workbook (hence my original
post mentioning that the chart could be deleted from the workbook after it
is
copied to PowerPoint). Any suggestions in this area?
You are not making 350 charts. You make the one Excel chart manually, it is
updated automatically when the macro changes the value in cell F1. The same
existing chart gets copied as a picture 350 times (after each time the value
in F1 changes) and pasted into PowerPoint 350 times on a new slide. So you
do need 350 slides, but copying pictures of the chart will not cause a huge
resource bottleneck.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______