I am currently making scatter plots. I have 26 columns, which each has its own plot. So I have the following source data.
=SERIES('[Modeling Project - all data.xls]Commercial Data'!$C$1,,'[Modeling Project - all data.xls]Commercial Data'!$C$1478:$C$1650,1)
Now column C does not get changed, but the range 1478 and 1650 gets changed for each group. For now, I have been manually updating each chart for the next range, but there must be a quicker way to do this. The charts are objects. I cannot change the formatting of the charts, so I must copy the tab (contains 26 plots) and make a new tab. On the new tab, I'm altering the source data(26 times) to the new range (next range would be 1651 to 1822). The ranges are never the same and it could be more or less then the previous.
Basically what I am looking for is a type of search and replace function to work directly with the chart objects. So I can perform a "find 1478" and "replace with 1651". The data and graphs are on seperate files.
Thanks for your help.
attached a sample of a variable scenario.
There are various ways to achieve this effect but this is quite simple for scenarios where the data is of fixed size. Which I infer is the case here.
Basically:
Setup a cell with a value that identify which data you want.
set up a working area for the copy of the data that is going in the chart.
set a formula to populate your working area with the correct data (here I have used indirect)
set the chart to work off the working area.
Hope this was useful or entertaining.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks