1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

worksheet copy question

Discussion in 'Business Applications' started by ron40, Apr 18, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. ron40

    ron40 Thread Starter

    Joined:
    Jul 6, 2004
    Messages:
    313
    First Name:
    Ron
    Sometimes I use "move or copy sheet" to copy a sheet to another file (worksheet). When i do that, the formulas in the copied sheet reference the original file. E.g. the file "jones.xls contains the worksheet "accuracy" which contains the cell formula "inventory!c11", referring to another sheet in jones.xls. I now want to copy the sheet "accuracy" to smith.xls. Smith.xls has the same sheet names (inventory, linearity etc) as jones.xls except that i've deleted accuracy from smith.xls because i want the new version there. The copied formula in smith.xls now becomes [jones template.xls]inventory'!F12 instead of "inventory!F12." I don't want [jones template.xls] to appear in a formula in smith.xls. Is there a way to do this?

    ron40
     
  2. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    There are ways around what you want to do but copying the sheets from workbook to workbook where a sheet corresponds different is hard to do. EG:

    Jones reference cell A1 shows something like =inventory!B3
    But inventory may have a reference their to another reference so the whole tree needs to be kept. IE: If A references B that references C you can't not bring over C and expect a similar result. I attached an example as simple as possible for a double relationship that works and how the 2nd one won't.

    The reference will change as the sheets change their cell values and have their corresponding associations change. Generally if you are doing an employee workbook where there are multiple relations it's important to know the tree well and not make too many duplicate workbooks for all you just create a formula for all to reference the same sheet, in the same workbook. Then if you had to move them around or save multiple files of workbooks, you may always delete the employees except for one or two.

    If you merely change the positioning or the association you can reacquire it quite easily one of two ways:

    1. If you open a new cell and hit "=" it starts a formula in most versions of Excel, well you may now choose another sheet at the bottom and click a cell, hit enter. Go back to the original cell and it knew you went to a different sheet and represented it.

    2. A representation of another sheet is easy it's just Sheetname + ! EG: Accuracy= Accuracy!. A relation from a cell to another cell in a different sheet may be something like: =Accuracy!A1.

    3. You may copy the formula for later if you are sure of associations or merely repeat the formula for more cells in a series by selecting the cell, moving the pointer to the lower right till you see a bold "+" then hold down the left button and go up, down, right, left, etc. Sometimes a copy of the formula is better though if you want multiple sheets to reference the same thing or formula. Just remember when you copy a formula after to hit enter because if you try to use the mouse somewhere it thinks you are still editing the formula.

    For big workbooks with changes each day, week, month, etc referencing similar variables that are set or don't often change I'd say macro or template. A good idea may be to show an example of what you have, then list a cell or two with a comment showing or cell describing how it changed and what you want it to continue to do.

    HTH
     

    Attached Files:

  3. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Formulas like to keep their dependencies
    • . Breaking them is not easy, nor should it be recommended. It's best to keep formulas in the same workbook unless you purposefully want to move them. If you move a sheet with references to another sheet, you'd need to move them both, or make the values static. You could always do a Find/Replace for the link value, or try to break the external links (which may cause you more of a headache).
     
  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    Once you have copied the page into the other wortkbook, lets say from Book1 to Book2, then do the folowing.

    Make sure you are in the worksheet in Book2 that you have just copied.

    Click on Edit, Replace, Find What Book1, Replace with Book2

    Then click on replace all

    As far as I can see that should sort the issue out.

    I am of course assuming I have grasped fully the implication.
     
  5. ron40

    ron40 Thread Starter

    Joined:
    Jul 6, 2004
    Messages:
    313
    First Name:
    Ron
    Sorry for the delay:
    The referenced cells e.g.: (=inventory!B12) contain constants, not references to other cells. I know what you are saying about the possibility of non-existent reference locations. Each of the quarterly files from jones.xls, smith.xls, etc contains the same sets of worksheets (accuracy, inventory etc) with the same formulas. Only the constants change between files. For the record: Each client has 1 "template" which contains their particular values. In that file are an inventory sheet, accuracy sheet etc. The inventory sheet has the constants (date of visit, isotope, activity, calibration date etc) while the other sheets within that file reference that inventory sheet. E.g. when I enter a date in inventory it is reflected in the others as well. When I make a visit to a client I use the template & save it under a new name appropriate for that visit. Sometimes I refine a template & want to put the changes into the other client&#8217;s templates. I&#8217;ve found that keeping the templates identical in outline makes it much easier to make changes later. If I revise a client&#8217;s template to refine it (group the sources differently etc) I want to make the same changes to the other client&#8217;s templates with their sources. That's when i do the copy. I thought that there might be something like "past-special" that places values not formulas but i guess not. I have been using &#8220; = &#8220; followed by <return> on the target & . it&#8217;s very simple but I thought there might be an even easier way.
     
  6. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,255
    There is a paste special option, Values, but that doesn't work with Copy or move sheet.

    So you could select all the data you want to copy and then got to the workbook where you want to paste it and then

    Select Edit, Paste Special...,
    Select Values or Values with number format and then click on OK

    It will only paste the values (result), not the formulas.

    Is that what you want?
     
  7. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Look here for something similar: http://excelusergroup.org/forums/t/617.aspx

    I would recommend you not break this information up into multiple files. There is really no reason why you shouldn't be able to have this data in the same file, even the same sheet. Would that not make it easier for inputing data?? Surely it would save you the trouble of making changes to multiple copies of files, that sounds like a royal pain in the back side.
     
  8. djangojazz

    djangojazz

    Joined:
    Apr 11, 2006
    Messages:
    301
    There is, it's called a macro. Record what you are doing:

    Excell 2003: Tools>Macros>Record New Macro
    Excel 2007: View I think>Right most in toolbar>Macros>new

    Make sure macro is recording

    Do all the things you would (calculations, listings that repeat, formatting)

    hit stop

    make one workbook a template for using the macro or spend a lot of time just making an in depth macro that it figured in it. Save others from the macro and save them individually. For repetitious tasks I always use a macro. I think the little I know about VBA is just from wanting to tweak my macros trying myself or asking here. You can create new sheets, automate tons of formulas and do it and then tweak your code later. I spend now 1/4 the time I used to in Excel when I use it because anything I do more than 4 times for more than 5 sheets I usually make a macro for.

    See my simple example for a macro, just make sure you enable macros or it won't work. Tools>Macros>Security>Set to low or medium>you can change back right after the example.
     

    Attached Files:

  9. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Frankly, it would be a lot easier (I'm no guru like the other guys, just practical and lazy) to copy the whole workbook.
    That way your new worksheet formulas would be tied to the duplicate pages of the new workbook. Then just delete the worksheets you don't want from the new workbook.
    But, like firefytr said, why do this anyway - it's a good way to end up with one file having one page updated but another file having another page updated - and never the twain shall meet.
    An alternative would be to have the reference cells not on another worksheet but in hidden columns - perhaps at the end of the worksheet - so that they get copied when the worksheet does.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Did'ya notice?! I changed my name!! :D Figured it'd be easier for people to recognize me. (y)
     
  11. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    Read right over it...!!!!!!!!!!!!!!!!!!!!!!!!
     
  12. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    How did you do that anyway? Went through all the profile options and saw nothing....
     
  13. jimr381

    jimr381

    Joined:
    Jul 20, 2007
    Messages:
    4,189
    First Name:
    Jim
    Just find the old file name and replace it with nothing. It will then reference the current workbook you are in and still retain the reference to the sheet tab as well.
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    @Loche: FAQ.
     
  15. ron40

    ron40 Thread Starter

    Joined:
    Jul 6, 2004
    Messages:
    313
    First Name:
    Ron
    Good suggestion but not quite appropriate for me. I have to keep my clients separated because each has different data (i.e. address, sources, claibration dates etc) & it becomes a nightmare when i want to issue reports etc. It's easier to use 1 file per quarterly visit because i can keep them grouped in each clients folder. I asked the question not because the task was onerous (it takes just a few minutes or so to hit "=" & navigate to the location) but i wanted to find a more elegant way to do it.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/705030

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice