Excel Filter: Copying graph from one workbook without retaining references.

October 7, 2008 1:34 PM

I have a fairly complex MS Excel Graph "utility" in its own workbook. I would like to add it to other Excel workbooks. When I use the "Move or copy selected sheets..." tool, it copies the graph and the graph data sheet as well, but all the data points in the graph refer back to the original workbook. How can I copy these sheets so that the graph data points refer only to the data in the new workbook?
posted by Rad_Boy to Technology (6 answers total)

Make a copy of the file outside of Excel, by using Explorer, and rename it. Then modify the source data in the copy as needed, either by entering new data or copying the data from another location.
posted by yclipse at 2:30 PM on October 7, 2008

In other words, yclipse is saying that you'd have to copy the source data at the same time - which isn't possible, unfortunately.

I considered making a macro to strip outside references from charts, since I occasionally do this, too. Might just go ahead & do it; if I find the time I'll post a link here.
posted by IAmBroom at 2:36 PM on October 7, 2008

Would find + replacing the references work? I do this with linked formulas all the time.
posted by goethean at 3:03 PM on October 7, 2008

Response by poster: I would like an easier way to do it, but one way I figured out is to use defined names rather than the relative references in the chart data. Would take awhile to do, though.
posted by Rad_Boy at 5:31 PM on October 7, 2008

Best answer: I think what yclipse is saying is that instead of copying the utility and bringing it to the data, you should bring the data to the utility.

1. Make a second copy of your utility sheet.
2. Copy the new data you want to analyze into the new copy of your utility sheet.

Now the references stay "in-file" and the data in that new file is analyzed by your utility. I've done this before. I actually have a folder of "utility" spreadsheets that do things I want/need frequently. For example, one of them is just a scatter plot/bar graph (on separate sheets) in normal, semi-log, log-log etc. Having this "utility" sheet lets me quickly visualize information without starting excel, properly formatting, each time.
posted by KevCed at 7:25 PM on October 7, 2008

Response by poster: KevCed (and Yclipse) were right on. Thanks to all!
posted by Rad_Boy at 10:36 AM on October 8, 2008