|
October 21st, 2009
 | 08:28 pm - Basic Excel feature request
This seems like it should be simple. I want to be able to make a worksheet full of data and charts showing assorted data summaries. I want to be able to cut copy and paste that into a new worksheet, and plug in a new day's batch of data. (So far, no problem). I would then love to be able to click a button that updates all of the graphs on the worksheet to the data in the same location on that worksheet.
I suppose I could probably use a template to do this, but A: don't wanna, B: I do make enough day-to-day changes to my data analysis that I would like to be able to easily choose between how I analyzed the data yesterday and how I analyzed it a week or three months ago, and C: I don't have enough faith in Excel to actually have gotten that far right.
The really frustrating thing is that this has gotten significantly harder since I upgraded to 2007, which is otherwise a significant improvement. Whereas before it could be accomplished with a right-click-left-click-enter or a right-click-tab-left-click-repeat-enter, it's now an complicated process that takes a couple minutes for each chart, which is really frustrating when I'm trying to update a dozen charts or so per worksheet while catching up on a week or two's worth of data.
[/vent]
Current Music: Doctor Who - Bad Wolf
|
Comments:
![[User Picture]](http://l-userpic.livejournal.com/1083397/463833) | | From: | ![[info]](http://l-stat.livejournal.com/img/userinfo.gif) xcorvis |
|---|
| Date: | October 22nd, 2009 01:23 pm (UTC) |
|---|
| | | (Link) |
|
Does "Refresh All" not do that? If I understand you right, you should right-click the worksheet tab, copy the worksheet, paste the new data in overwriting the old data and then hit refresh all.
If you're manually copying stuff from one sheet to the other, it's probably keeping the graphs pointed at the original sheet rather than updating them to point to the new sheet.
That is in fact exactly the problem I'm griping about, that you have to manually go into each data set of each chart and change the link to the current worksheet from the previous worksheet that was copied. "Refresh All" only works if you change the original data, but I want to keep the original data in place where it was and start a new worksheet with the same layout, but different data (because one worksheet will be Tuesday's experiment, and the next will be Wednesday's).
| | From: | xcorvis |
|---|
| Date: | October 22nd, 2009 09:32 pm (UTC) |
|---|
| | | (Link) |
|
OK, good, I did understand. So does copying the entire worksheet work better than copying the data into a new, blank worksheet?
Now I don't think I understand. If you're talking about just copying the data, the problem is then making all the same charts again from scratch on each new worksheet, which would be even more time-consuming. What I've been doing is just making a new worksheet in the same file, selecting all, copying, pasting, and then deleting the data from the previous worksheet and replacing it with the new data.
| | From: | xcorvis |
|---|
| Date: | October 23rd, 2009 12:21 am (UTC) |
|---|
| | | (Link) |
|
What I'm talking about is different, and hopefully faster. Look at the bottom of your excel doc - find the TAB for today's worksheet. Right click on it. One of the options is "MOVE or COPY". Select it. Copy the worksheet by checking the box at the bottom of this new window. It should duplicate the entire worksheet, including data and graphs. Then right click on the tab for the new worksheet and select RENAME.
After that, you just paste in your data and refresh.
![[User Picture]](http://l-userpic.livejournal.com/2576732/272194) | | From: | malcubed |
|---|
| Date: | October 23rd, 2009 06:16 am (UTC) |
|---|
| | Holy shit it works! | (Link) |
|
Thank you so much!
You are SO AWESOME!
YOU HAVE MY GRATITUDE!
WE FORGE OUR TRADITION IN THE SPIRIT OF OUR ANCESTORS!
| | From: | malcubed |
|---|
| Date: | October 23rd, 2009 06:20 am (UTC) |
|---|
| | Re: Holy shit it works! | (Link) |
|
...but only *some* charts automatically shift?
| | From: | xcorvis |
|---|
| Date: | October 23rd, 2009 07:15 pm (UTC) |
|---|
| | Re: Holy shit it works! | (Link) |
|
Eh, beats me.
;)
| From: | elfdope |
|---|
| Date: | October 25th, 2009 02:30 am (UTC) |
|---|
| | | (Link) |
|
I would visual basic up a macro on page 1 that saves it to a new tab, and then goes blank awaiting your new data to be entered. Although that might be too much work. It really depends how long you plan to be using the sheet. |
|
|
