Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post

    Need to create linked sheets that can be set up as a master template

    I have a work requirement that currently uses three different spreadsheets with the same information in a number of places between them.
    Right now, I have to keep copying the values from a number of cells from one workbook to another, which is very tedious.
    We are using Excel 2007, but have to use the XLS format for compatibility with other users who have 2003
    I was able to set up the three different books as three sheets in the same workbook, but when I use the =sheet1!a1 function, it asks for the name of the workbook.
    the issue is that we make many of these sets of sheets with different file names and having to change all of the references would be more work than just doing the copying we do now.
    Is there a way to set this up so when I open a new book and enter data into the main page, the referenced cells copy to the other sheets without knowing the new name?
    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Why do copying? Why not just link the master data to the appropriate formula cells in the worksheet. Then changes to the master will be reflected in all the dependent cells without doing any coding...


    [Or am I misunderstanding the problem??]
    Steve

  3. #3
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post
    Steve,
    There are no formulas involved.
    I have information that gets entered into various cells on one sheet, then the same information has to be copied into different cells on the other sheets.
    What happens is that the sheets all have similar names (XYZ Test, XYZ Setup) for this project.
    The next project is ABC, so there is ABC Test and ABC Setup, all in a folder called ABC.
    Unless I did something wrong, when I had the two sheets in the same workbook, Excel asked for the name of the workbook to reference when I used =sheet1!a1. There will be about 15 or so references like this throughout the second and possibly third sheet.
    It will be very difficult to keep providing the correct name and that will take more time than just doing the copy and paste I do now.
    I want the reference to just be to the cell in sheet 1 without needing a file name.
    Is that clearer?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    But =sheet1!A1 is a formula.

    To enter them, just enter an equal sign (=) then select the sheet it should refer to, select the cell and hit [enter]. The sheet!cell reference will be created. Manually you may need to add single quotes(') if the name contains a space: (eg ='Sheet 1'!A1)

    Steve

  5. #5
    Star Lounger
    Join Date
    Jul 2011
    Posts
    57
    Thanks
    5
    Thanked 1 Time in 1 Post
    Steve,
    As I was messing around with it, I may have solved it with a similar formula.
    In the "receiving" cell, the formula ='sheet 1'!$A$1 does work. I'm not sure what the difference is with teh $, can you explain that so I understand what I did?
    Thanks,
    Jeff

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The Dollar signs only are important if copying the formula. They "lock" the cells on the row and/or column or the copying will be relative.

    For example, imagine you have the formula in Sheet2!A1='sheet 1'!$A$1. The $ before the A means you are "locked" on Col A (Col A is Absolute), and the $ before teh 1 means you are locked on row 1(row1 is absolute). So if you copy Sheet2!A1 toSheet2!C3, the formula in cell Sheet2!C3 will still be ='sheet 1'!$A$1.

    If the formula in Sheet2!A1 is ='sheet 1'!$A1 (Abs Col A, relative row 1) the formula in Sheet2!C3 will be ='sheet 1'!$A3
    If the formula in Sheet2!A1 is ='sheet 1'!A$1 (relative Col A, Absolute row1) the formula in Sheet2!C3 will be ='sheet 1'!C$1
    If the formula in Sheet2!A1 is ='sheet 1'!A1 (relative Col A, relative row1 ) the formula in Sheet2!C3 will be ='sheet 1'!C3

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •