Another Microsoft Question

By -
OK kids, there's a five spot for whoever can answer this first (payable in Nashville): I have an Excel document with several sheets. Right now one of the sheets points to an earlier one; i.e. there are lots of cells in it that say stuff like "'May budget'!C6".

Now I want to copy the entire sheet into a different version of this same document. I.e. the sheets and their names etc. are all identical. The problem is, when I copy and paste the sheet from my version onto the "same" sheet in the other document, Excel quite understandably is still linking those cells to the May budget sheet in the first document. I.e. I want to paste it in such that the cells point to the 'May budget'! in the same file.

Any thoughts?

Comments

  1. Jim12:09 AM

    I usually handle this by moving BOTH sheets over and then updating the "May budget" sheet with the revised data. If that's not practical, though, you'll need to do a Find-Replace, where the find is the filename string with the appropriate bracketing/quoting, and the Replace field is left blank.

    ReplyDelete
  2. Ben Osborne3:15 PM

    Another way to do this is by using a macro to loop through every cell of the destination worksheet, replacing the formula of each cell with the formula from the corresponding cell of the source worksheet.

    Here is an an Excel workbook with the code to do that as a userform that makes it as easy as copying a sheet from one workbook to another. If you have Excel 2007, the button to lauch the userform will on the "Add-Ins" tab. If you have Excel 2003 or earlier, it will appear on a custom toolbar.

    You may have to adjust your macro security settings in order for macros to run. Since the code is unsigned and I am not a trusted source, you should probably examine the code first to make sure it's not doing anything malicious.

    ReplyDelete
  3. Anonymous12:03 PM

    After pasting the data into the 2nd workbook, select Edit | Links from the menu. This will let you change all references pointing to the old book to now point to the new one.

    ReplyDelete
  4. Anonymous12:05 PM

    Note on the above: you need to save the 2nd book before editing the link, so that you can navigate to it in the file dialog.

    ReplyDelete
  5. Bob Murphy10:55 AM

    Thanks guys. I think I'll try your suggestions in order of 3, 1, and 2 (since macros scare me).

    ReplyDelete
  6. Anonymous12:23 AM

    Welcome to our game world, wakfu kamas , wakfu gold , buy wakfu kamas , wakfu money and wakfu kama , they are very interesting.

    ReplyDelete

Post a Comment

Popular posts from this blog

Libertarians, My Libertarians!

By -
Cruel to be kind means that I love you . Because, while I think you are mistaken, your hearts are in the right place -- yes, even you, Silas --  unlike some people . This Breitbart fellow (discussed in the link above), by all appearances, deliberately doctored a video of Shirley Sherrod to make her remarks appear virulently racist, when they had, in fact, the opposite import. I heard that at a recent Austrian conference, some folks were talking about "Callahan's conservative turn." While that description is not entirely inaccurate, I must say that a lot of these people who today call themselves conservative give me the heebie-jeebies.
Read more

"Pre-Galilean" Foolishness

By -
 I am currently reading The Master and His Emissary , which appears to be an excellent book. ("Appears" because I don't know the neuroscience literature well enough to say for sure, yet.) But then on page 186 I find: "Asking cognition, however, to give a perspective on the relationship between cognition and affect is like asking astronomer in the pre-Galilean geocentric world, whether, in his opinion, the sun moves round the earth of the earth around the sun. To ask a question alone would be enough to label one as mad." OK, this is garbage. First of all, it should be pre-Copernican, not pre-Galilean. But much worse is that people have seriously been considering heliocentrism for many centuries before Copernicus. Aristarchus had proposed a heliocentric model in the 4th-century BC. It had generally been considered wrong, but not "mad." (And wrong for scientific reasons: Why, for instance, did we not observe stellar parallax?) And when Copernicus propose...
Read more