change source data in multiple charts

Hi
I have over 30 charts where I need to change the source data and it's taking 
ages doing it manually, especially as each chart has 3 or 4 series. Is there 
an easy way of doing this, please?
I've tried find and replace, but that doesn;t work
Thanks
0
Tuxla (9)
7/4/2008 10:58:01 AM
excel.charting 18370 articles. 0 followers. Follow

5 Replies
470 Views

Similar Articles

[PageSpeed] 15

We need a little more detail.  1. Are you modifying all the charts to use 
the same new range or does each chart use a different new range?  Is the 
data compact, that is, if you choose the chart wizard and look at the Data 
Range tab does the Data Range box contain a simple reference like 
=Sheet1!$B$2:$E$7?

Thanks,
Shane

"Tuxla" <Tuxla@discussions.microsoft.com> wrote in message 
news:92457D68-7954-4FB4-9B66-FD26748DA8B1@microsoft.com...
> Hi
> I have over 30 charts where I need to change the source data and it's 
> taking
> ages doing it manually, especially as each chart has 3 or 4 series. Is 
> there
> an easy way of doing this, please?
> I've tried find and replace, but that doesn;t work
> Thanks 

0
7/4/2008 2:27:03 PM
Hi
The charts will all be using different data ranges, but they are all in the 
same column, which is why I thought find and replace might work.
Yes, the data is compact. All I want to do is change the column letter (eg 
from column B to column C)
Thanks

"Shane Devenshire" wrote:

> We need a little more detail.  1. Are you modifying all the charts to use 
> the same new range or does each chart use a different new range?  Is the 
> data compact, that is, if you choose the chart wizard and look at the Data 
> Range tab does the Data Range box contain a simple reference like 
> =Sheet1!$B$2:$E$7?
> 
> Thanks,
> Shane
> 
> "Tuxla" <Tuxla@discussions.microsoft.com> wrote in message 
> news:92457D68-7954-4FB4-9B66-FD26748DA8B1@microsoft.com...
> > Hi
> > I have over 30 charts where I need to change the source data and it's 
> > taking
> > ages doing it manually, especially as each chart has 3 or 4 series. Is 
> > there
> > an easy way of doing this, please?
> > I've tried find and replace, but that doesn;t work
> > Thanks 
> 
> 
0
Tuxla (9)
7/4/2008 2:41:00 PM
I've built a small utility that manages simple changes for you. To change a 
column, change (for example) $A$ to B. If you just change A to B, it may 
also change the letter A in a sheet name.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Tuxla" <Tuxla@discussions.microsoft.com> wrote in message 
news:92457D68-7954-4FB4-9B66-FD26748DA8B1@microsoft.com...
> Hi
> I have over 30 charts where I need to change the source data and it's 
> taking
> ages doing it manually, especially as each chart has 3 or 4 series. Is 
> there
> an easy way of doing this, please?
> I've tried find and replace, but that doesn;t work
> Thanks 


0
jonxlmvpNO (4558)
7/4/2008 5:38:18 PM
that's great, very useful. thank you

"Jon Peltier" wrote:

> I've built a small utility that manages simple changes for you. To change a 
> column, change (for example) $A$ to B. If you just change A to B, it may 
> also change the letter A in a sheet name.
> 
> http://peltiertech.com/Excel/Charts/ChgSrsFmla.html
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "Tuxla" <Tuxla@discussions.microsoft.com> wrote in message 
> news:92457D68-7954-4FB4-9B66-FD26748DA8B1@microsoft.com...
> > Hi
> > I have over 30 charts where I need to change the source data and it's 
> > taking
> > ages doing it manually, especially as each chart has 3 or 4 series. Is 
> > there
> > an easy way of doing this, please?
> > I've tried find and replace, but that doesn;t work
> > Thanks 
> 
> 
> 
0
Tuxla (9)
7/7/2008 7:27:04 AM
Hi,

We need more detail - is each chart being plotted from the same range or is 
each chart plotted from a different range?  If it’s the "source data" that 
is changing why do you need to change anything - charts are dynamic and 
update automatically?  If you are plotting different ranges when the data 
changes, how does the new data range relate to the old data range?  Does it 
move one column to the right, or down one row, or what.  Is the new data in 
a completely different part of the spreadsheet/workbook and if so how do you 
know where it will be?

Give us an example - the original data plots A2:C20 and the new plot will be 
D2:F20 or what ever.

Thanks,
Shane

"Tuxla" <Tuxla@discussions.microsoft.com> wrote in message 
news:92457D68-7954-4FB4-9B66-FD26748DA8B1@microsoft.com...
> Hi
> I have over 30 charts where I need to change the source data and it's 
> taking
> ages doing it manually, especially as each chart has 3 or 4 series. Is 
> there
> an easy way of doing this, please?
> I've tried find and replace, but that doesn;t work
> Thanks 

0
9/4/2008 2:38:52 PM
Reply:

Similar Artilces:

Excel>Data>Import External Data>New Web query #2
Hi I am trying to use Excel 2003 to import data from http://bigcharts.marketwatch.com/quotes/default.asp?refresh=on&rand=8519 I use new web query on the above url. I select the data in the block under 'My Favourite Quotes' The wizard behaves as expected but only returns 1 line not the 3 that have been selected. Can someone please give me some pointers on how I can get the 3 lines. Thanks Bruce Try yahoo instead or go here and get my FREE excel file for quotes or history. Look for donaldb36 as author. xltraders@yahoogroups.com -- Don Guillett SalesAid Software donaldb@...

Changing Font Characteristics Within a Formula
How do I change the font color and make it bold within a formula? I am trying to link two different cells with text in them to one cell and I would like to have the color and font weight changed for just one of the links. For example... A1=1 A2=2 I want B1 to show 1(black normal font), 2(red bold font) Thanks. -- jdurrmsu ------------------------------------------------------------------------ jdurrmsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27122 View this thread: http://www.excelforum.com/showthread.php?threadid=467599 What you want is called ...

Select multiple files error.
I want to select multi files using CFileDialog: CFileDialog FileDlg (TRUE, NULL, NULL, OFN_ALLOWMULTISELECT ); TCHAR strBuffer[4096]; FileDlg.m_ofn.lpstrFile = strBuffer; FileDlg.m_ofn.nMaxFile =sizeof(strBuffer); if (FileDlg.DoModal()==IDOK) { CString csFname; CStringArray m_strArray; m_strArray.SetSize(0); POSITION pos = FileDlg.GetStartPosition(); while (pos != NULL) { csFname = FileDlg.GetNextPathName(pos); TRACE("%s\n", csFname); m_strArray.Add(csFname); m_CListBox_ListBox.AddString(csFname.GetBuffer()); } } The error is that FileDlg.DoModal()...

Counting frequency in unsorted data
hi...I'm looking for a function that will count frequency using multiple variables in an unsorted data set using two variables. I can get the information that I need by sorting the data and then using Data->Autofilter and using the countif function ti isolate the variables individual. However, this is problematic if the data gets re-sorted. Data example A B C XA GM 125 XC BA 34 XA GM 23 XC NY 19 XA GM (blank) I'm looking to count the number of occurrences of XA where the value of GM i...

How do I configure multiple lines per opportunity
We are using MS Dynamics CRM 4.0 We need to implement a solution to support our pipeline forecasting. Our opportunities are essentially "projects" that have multiple invoices spread over the life of the project. I would like to configure CRM so that each opportunity_product has a start and finish month offset from the expected close date of the opportunity so that I can create a pipeline forecast showing the expected revenue spread. I have created new attributes in the opportunity_product for start and end month as an offset from the close_date but how can I calculate the ac...

Pasting chart worksheet as link 2007
A chart created in 2007 and sitting in it's own sheet (not included on the data worksheet) will not display entirely if pasted as link (neither into Word nor PPT) Some of the chart seems to be "cut off" in the linked object. This seems to be connected to how much of the "canvas" is visible in Excel. Viewing the chart in Excel on a zoom so that it fills the whole screen seems to remedy this. Is there some setting in Excel that would allow us to set the worksheet chart to automatically size to window? Or else: does anyone know a way of pasting a worksheet chart as ...

Multiple criteria for "Is visible" property
The following is part of a report page in Access 2000 I have a text box called “txtgroupcount” with a control source of Count(*) in Groupheader0 I also have the following in the “On Format” event for the following three sections Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) Me.Detail.Visible = (Me.txtgroupcount > 9) End Sub Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer) Me.GroupHeader0.Visible = (Me.txtgroupcount > 9) End Sub Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer) Me.GroupHeader1.Visible = (Me.t...

Change Users AD Name
We installed CRM with a domain admin and would now like to change the AD entry for that user. We would like to know is there any CRM downstream hazzard in changing that administrator's user name in AD? Yes. Changing the AD login breaks the tie to AD as it looks at both the login name and the SID. There are a few options to fixing this, I remember seeing a KB article recently that outlined how to synch CRM up with this type of change. 1) Go into Deployment manager and expand User manager 2) Right click on the account in question and select Change Account 3) Choose a new AD accou...

MFC Gantt chart ???
Hi All, I need a good Gantt chart Lib for MFC. Anybody know about anyone? Thanks. ...

Can I change the "white cross" cursor in Excel to another cursor?
I don't particularly care for the "white cross" cursor in Excel. Can it be changed to another cursor? Not an option that anyone has been able to alter as far as I can recall. Gord Dibben Excel MVP On Tue, 3 May 2005 10:23:01 -0700, "KFEagle" <KFEagle@discussions.microsoft.com> wrote: >I don't particularly care for the "white cross" cursor in Excel. Can it be >changed to another cursor? ...

How do I change a spreadsheet from English to Spanish?
I'm now working in a bi-lingual facility and need to create several spreadsheets in Spanish that I already have saved in English. Is there a way to change the language in an existing spreadsheet? Thanks..... The formulas will be translated automatically, if the spreadsheet is loaded into a Spanish version of Excel. There are a few exceptions, like functions from Analysis Toolpak. Text cells will not be translated automatically. -- Kind regards, Niek Otten Microsoft MVP - Excel "Steve@bepc" <Steve@bepc@discussions.microsoft.com> wrote in message news:D5304E1D-637...

Hyperlinks and sorting data
I have two sheets in my Excel 2003 workbook. On both sheets column A contains a hyperlink in each cell that will jump to the next sheet of that same cell. For example, if the user clicks on A3 of sheet 1 the hyperlink will take them to sheet 2 cell A3. Sheet 2 is set up the same way with hyperlinks. The problem I ran into is if I sort the data - columns A thru D, on sheet 1 - the hyperlinks won't work on sheet 2 because the cell reference changes on sheet 1 when I sort the data. Is it possible to create a hyperlink or something in Excel that matches text and will jump back and ...

Export Data from B-Trieve to EXCEL
I am upgrading Great Plains from 4.0 to version 9.0. The current data is in B-Trieve version. Is there any way that instead of Migrating data from B-Trieve to SQL, I export data from B-Trieve to Excel and then import it in SQL via integration manager. Any help in exporting the data from B-Trieve to Excel would be highly appreicated. Thanks It's possible to export from Btrieve to Excel. GP used to provide a DDF creator that would enable you to create the five DDF files that would give you access to your data via ODBC. But, are you planning on exporting each file to Excel and then...

Question about charts
If I have two lines in a line graph and they criss-cross (like a supply and demand curve), how can I find the exact values on the x and y axis where the two lines cross? Is this possible by using Excel? Let's hope you made XY chats (no Line charts) - change chart if necessary Insert Trendlines for both (better yet use SLOPE and INTERCEPT functions) So Now you know y1=m1x1+b1 and y2=m2x2+b2 At point of intersection y1=y2 and x1=x2 m2x+b2 = m1x+b1 x= (b1-b2)/(m2-m1) y = m1x + b Its all rather simple algebra. Bernard (you can call me Prof <grin>) www.stfx.ca/people/bliengme &quo...

Drag & Drop Changes in Outlook 2007
Outlook’s Drag & Drop functionality works differently in Office 2007 then it does in Office 2003. When I right click to Drag and Drop an E-mail from an OL folder to my desktop, I am given the option to Move or Copy the Item. When I select Move, the item is moved to the desktop. In OL 2003 the moved item will be automatically placed in the OL Deleted Items Folder. In OL 2007 the item is not automatically placed in the Deleted Items Folder. How do I restore this functionality in Office 2007? I am Running Office 2007 SP2 under Windows 7 Ultimate. ...

How do I create a blank chart?
I am trying to create a blank chart to be used after it is printed (filled in by hand by several different people). It only needs to be a simple column graph that allows users to fill up the column based on their results on a certain date. Hi, Just create a chart the refers to the blank range. To make it easier enter some dummy data and create the chart, then remove the dummy data. -- Thanks, Shane Devenshire "cheri3107" wrote: > I am trying to create a blank chart to be used after it is printed (filled in > by hand by several different people). It only needs to b...

Change Accounting Years
We are changing our accounting year in about two weeks, to be completed before Dec 31. The company has been using a fiscal year running APR 1 through MAR 31 since installing GP in 1998. Our current fiscal year, 2008, will be renamed 2007 and run from APR 1 through DEC 31. FY 2007 will be renamed to FY 2006, and so forth back to the first year in the system. (The actual procedure is to rename the oldest years first, 1998 ==> 1997, 1999 ==> 1998, etc.) 1/1/2008 will then become the first day in the new accounting year, 2008. We have made the changes on a test server, using the...

Charting Text #2
Below sample data I am trying to chart but I'm not sure if Excel can do this. It is basically a record of animals (tags) and where they have been spotted. I am trying to create a chart (maybe a scatter) with Location and Month on the X & Y axis and the tag data to appear to show a sighting. I have created a work around using a pivot table but the person looking at this chart is not an Excel user and I want to keep it simple. Any suggestions? Location Month Tag NW Jan J33 NW June J34 SW May J33 SW June J35 E Oct J36 E Nov J35 W May J33 W Apr J34 Hi, Insert new blank columns ...

Chart data from text file
I have my x- and y- values as tab-separated values in a text file. There are many different files, so it is too cubersome to open them all convert them all to Excel. Is there any way I can create a chart that reads its data from a text file? On Sun, 16 Sep 2007, in microsoft.public.excel.charting, hmm <hmm@discussions.microsoft.com> said: >I have my x- and y- values as tab-separated values in a text file. There are >many different files, so it is too cubersome to open them all convert them >all to Excel. > >Is there any way I can create a chart that reads its data fr...

New Bank Data Download to Money 2002
OK, I still have Money 2002. When MS Passport went away, so did my ability to pull data from within Money. However, I could go to my institutions, select download transactions to Money format, and they would import right into Money. My bank, Chevy Chase, just changed ownership to Capital One. Their new web site only downloads an Excel comma delimited spreadsheet, rather than going straight to Money. Is there any way I can pull a set of transactions over a date range directly? In microsoft.public.money, Dimitrios Paskoudniakis wrote: >OK, I still have Money 2002. When MS Pass...

Filter Choice in Chart Title
Is there a way to reference a filter choice in a chart title? I have a column with about 7 validsets. If the user choses one of those, I would like the choice to be displayed in an associated chart title. I understand that I can reference a cell in the chart title, so really the question is; How do I reference a selected filter choice in another cell? Unfortunately I can't use pivots for this, since the chart is an XY scatter. Thanks, Kevin You could use this UDF in a cell. Put the code in a REGULAR module and type =st() somewhere. Function st() x = Cells(Rows.Count, "b&...

Bubble chart basics
Using 2000 I am trying to come up with a meaningful sample of a bubble chart for the charting class I teach and I'm having a really tough time. I know nothing of bubble charts. I have been searching the group and web for some time now trying to figure out where I'm going wrong. Here's my data: Region Avg Stay Patients North 1.8 1208 East 2.3 2304 South 1.1 961 West 3.4 1936 Here's what I want to get when I use the bubble chart - the region names as the X axis values, the average stay as the y axis...

3-D Chart background picture prints only partly
Hi, I have a problem in printing Chart with background picture: The Chart type is Clustered Column with a 3-D visual effect. I right-click the background and choose Format walls - Fill effects - Picture - Select Picture. I choose a .jpg picture. Format = Stretch. Apply to Sides, Front and End. The picture shows beautifully and prints fine on a black- and-white printer. But printing to a color printer (HP DeskJet or Color LaserJet) only prints 1/9:th of the picture starting from the upper left corner and stretching it to cover all the chart background. Print Preview shows the pictu...

collect data from the same row in multiple spreadsheets
I have 140 employees with a separate workbook for each.The workbooks contains their by-weekly timesheets. Each workbook has 26 sheets (one for each pay period). I need to start a workbook that will contain the total line for each employee (line 37). For instance, The first sheet in each workbook in named "july 3". I need to collect line 37 from each July 3 sheet (total 140 lines) and have them show in the new workbook on the "july 3" sheet. I have to do this 26 times to complete the new workbook. Does anyone know of a shortcut? Hi Jeannine > of a shortcut? ...

Daily Importing of External Data using Web Query
Every month I create a spreadsheet from a template by creating one ta for each day of the month named after day of the month/year (010104). then maually enter a Web Query on each tab which downloads dail weather information for an insect life cycle model. This is a very time consuming and tedious task because the onl difference in the links is the expression "20040101" (for January 01 2004) to "20040102" (for January 02,2004) INCLUDED IN THE LINK. Is it possible to use a macro (VBA code) to enter the Web Query addres in cell A1 on each tab when the workbook is created an...