Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2003
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Changes to linked worksheets (Excel 2000 SR-1)

    Hi,

    I'm hoping someone can help me with what I hope is an easy fix. In summary, in a single workbook, I have 4 worksheets. Column A in all worksheets contains the same information, a list of 700 pieces of equipment. What I want to do is automate my update process. If I need to add a new piece of equipment, I would like to add it to Column A in Worksheet 1, and have that change occur automatically in the other 3 worksheets. The catch is, the new piece of equipment (or entry) won't be added at the end of the list. Most often the new piece of equipment will need to be inserted some place in the middle (i.e. between line 350 and 351). My original thought was to simply link the cells, i.e. in Cell A5 on worksheets 2, 3 and 4, would be the formula: ='Worksheet 1'!A5 Meaning, whatever typed into A5 on Worksheet 1 would appear in A5 on worksheets 2, 3 and 4. I thought that by applying this formula to the entire Column A for worksheets 2, 3, and 4, I would accomplish my goal. The problem arose when I needed to insert a new row. Going back to the example above, if I needed to insert a new piece of equipment between lines 350 and 351, when I inserted the new row in Worksheet 1, that same row insertion operation doesn't transfer to the other worksheets. A351 on Worksheet 1 is now blank (the newly inserted row), but A351 on sheets 2, 3 and 4 displays the data that is now in A352 on sheet 1 (formerly A351 before the row insertion). I apologize if this sounds confusing. To demonstrate it in Excel would be easy, trying to put it into words is a little more difficult [img]/forums/images/smilies/smile.gif[/img]

    At any rate, if someone can please give me an idea how to tackle the automatic update process I'm trying to figure out, I would really appreciate it.

    Many thanks in advance.

    dg

  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

    Re: Changes to linked worksheets (Excel 2000 SR-1)

    You don't give a lot of details of why you need this done. The question sounds similar to that asked in <post#=296982>post 296982</post#> though his was linked worksheet and NOT linked workbooks.

    Without understanding your spreadsheet design and purpose I can not give a definitve answer, though I would suspect a redesign might be the best course. I think a design that ONLY has one master list is preferred to trying to keep 4 lists "in-sync".

    It could be done with macros, but it can get complex to keep track of all the changes and you would have to protect all the workbooks and allow inserting/deleting/adding/editing etc via code and when one is changed, the same change gets applied to all: Insert a row in workbook A, insert the same row in Workbooks B, C, D. Add an item to any of them, add it to all, etc.

    I think by the time you work out all the code, you could have just redesigned it to prevent the problems from occuring.

    Steve

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Changes to linked worksheets (Excel 2000 SR-1)

    This can easily be done by assigning a 'name' to the rows in col A that you want deal with. I selected rows A1:A13 and named it TEST (using the name box or from the Insert/Names/Define menu). I did this on Sheet1. Then on Sheet2 in cell A1 I added formula "=TEST". I copied that down to row 13 (since that's all that was defined). This automatically filled in the same data that was on Sheet1.

    Now when you add/edit rows on Sheet1, those same cells on Sheet2 are updated.

    Does that work for you? I attached a sample.

    Deb <img src=/S/burga.gif border=0 alt=burga width=40 height=15>

Posting Permissions

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