Janie;324208 Wrote:
Have mulitple charts. Source Data is :

='[different file]!sheet name'!range.

The sheet from the different file has now been copied into the file
where
charts are. Is there an easy way to remove the different file name
from all
the Source Data references (for Name, XValues and Values) such that
reference
becomes simply:

='sheet name'!range
Right off I can't think of any EASY way to do it, but there are several
options you could use. It would help to know if you are just linking
straight from the chart workbook or if you are using code to do the work
of populating the cells. If it's only a matter of changing the formula,
look up the Formula and FormulaR1C1 functions in VBA and you can then
change any cell(s) you want, like 'Sheets(1).Cells(1, 1).FormulaR1C1 =
"Hello"' will change the contents of A1 to 'Hello' in the first
worksheet. If you are new to VBA, you will need to do a little more
research and learn how to use the parsing functions to find the
'[different file]!' name and truncate it from the formula, then put it
back in. It all depends on what you are wanting to do with this and
how...


--
gmorris
------------------------------------------------------------------------
gmorris's Profile: http://www.thecodecage.com/forumz/member.php?userid=245
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=90600