Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to change source data in Excel Charts
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to change source data in Excel Charts
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to change source data in Excel Charts
no - this is not changing any formula in any cell. This is changing the
REFERENCE in the Source Data dialog. If not using the Source Data dialog, then the series reference that is =SERIES('[FileName]SheetName'!Range) If this was just a case of changing references in a Cell, well, then heck, Find and Replace would do the trick just fine. But Find and Replace does not touch the charts. Given that there are 50 charts each having two dozen data points, I do not relish the prospect of going point by point to correct the reference to become =SERIES('SheetName'!Range) That's why I am looking for a nice way to loop through the SeriesCollections and change the reference. Any takers on the challenge? "gmorris" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to change source data in Excel Charts
Go to Edit menu Links, find the other workbook in the list and select the
link, click Change Link, and browse to the file you have open, Next time, copy the charts using this approach: http://peltiertech.com/WordPress/mak...k-to-new-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Janie" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to change source data in Excel Charts
OMG! Too simple. You're a prince, Jon!
"Jon Peltier" wrote: Go to Edit menu Links, find the other workbook in the list and select the link, click Change Link, and browse to the file you have open, Next time, copy the charts using this approach: http://peltiertech.com/WordPress/mak...k-to-new-data/ - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ Advanced Excel Conference - Training in Charting and Programming http://peltiertech.com/Training/2009...00906ACNJ.html _______ "Janie" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop to change source data in Excel Charts
Ahh, now that makes a lot more sense! Sometimes just a little more info on what one is trying to do makes all the difference. -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying charts with automatic change of data source | Charts and Charting in Excel | |||
Cannot change data source for copied charts | Charts and Charting in Excel | |||
change source data in multiple charts | Charts and Charting in Excel | |||
How to keep source data for the Excel Charts constant ... Please H | Charts and Charting in Excel | |||
How do I create charts that change source data automatically? | Charts and Charting in Excel |