The W
Views: 95697665
Main | FAQ | Search: Y! / G | Calendar | Color chart | Log in for more!
20.4.14 1830
The W - Internet & Computers - Excel Question - Graphs
This thread has 5 referrals leading to it
Register and log in to post!
Thread rated: 5.00
Pages: 1
(787 newer) Next thread | Previous thread
User
Post (2 total)
Rudoublesedoublel
Potato korv








Since: 2.1.02
From: Kentucky - Home of the 8 time NCAA Champ Wildcats

Since last post: 5 days
Last activity: 1 hour
#1 Posted on | Instant Rating: 5.00
I'm working on some reports at work and am hoping that there is an easier solution to my problem than the one that I've found.

I am using an Excel workbook with 12 worksheets (tabs) - I have one tab for each month of the year. I have 11 graphs on each tab. In order to speed up my production time, I created the graphs (and data layout-which are all links to separate spreadsheets) for my "January" tab and worked with it until I had what I wanted. I then copy the completed tab and paste it to all of the other tabs. It is easy enough for me to do a "find and replace" to change the reference codes for the cells with links, but here is what I'm having a problem with: All of the graphs on every tab refer to my "January" tab. The find and replace won't make them refer to the proper tab. I can change the tab reference by clicking on the graph, selecting Source Data from the drop-down menu, and manually changing the tab reference for each data range. Thus my question - is there an easier way to copy a bunch of data and graphs from tab a paste them in tab b and have the graphs in tab b look to tab b for their values instead of tab a? OR - Am I just going to have to manually change this in every graph on every bleeping tab?

Google hasn't been kind in my search for this - many thanks for any suggestions.
Promote this thread!
HairRaiser
Kishke








Since: 13.1.03
From: S. Attleboro, MA

Since last post: 2424 days
Last activity: 1760 days
#2 Posted on | Instant Rating: 5.00
If you haven't broken down and done it manually by then, I can probably write you a bit of VBA to do it at work on Tuesday, although I may not be able to get it PM'd to you until I get back home.

From the sounds of it, the source data tabs are all set up identically, so all that needs to be updated is the tab name, which is based on the month, right?

MSDN used to have easy to navigate Excel object model documentation but they've redesigned the pages (at least the ones I'm looking at) and I'm having trouble doing this from memory. :-)

I did write something a few years back that found all the external links and wrote out the locations to a text file. It'll be easy enough to modify that code to just do the replacements.

EDIT: I sent you a ZIP file at the email address in your profile. Good luck!

(edited by HairRaiser on 12.1.05 1719)


March 22, 2004 - Finally the WotD!
Thread rated: 5.00
Pages: 1
Thread ahead: Gmail problems
Next thread: New Micro-AmigaOne and Amiga OS4
Previous thread: PHEL: Update your virus definitions... XP SP2 affected...
(787 newer) Next thread | Previous thread
It was a pink iPod mini, which means that it had a hard drive. There is such a small chance that it is working that I wouldn't even speculate as to the odds. Even if it had been air tight at first it probably had sewage weep into it eventually.
- Guru Zim, Don't flush your iPod (2006)
The W - Internet & Computers - Excel Question - GraphsRegister and log in to post!

The W™ message board

ZimBoard
©2001-2014 Brothers Zim

This old hunk of junk rendered your page in 0.054 seconds.