Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2011
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic Content in Excel?

    I have a number of rows that I want them to be reflected on another sheet, so I am using the paste “link”.

    However, this means that I always have to make the changes in the source. I would like to be able to make the changes either in the source or the target. In fact, I don’t care which is source or target.

    Is there a way to reproduce the content so that I can do this?

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    113
    Thanks
    8
    Thanked 15 Times in 14 Posts
    My advice would be to decide which is to be the source and which the target. "I don't care which" suggests to me that later "I don't know which" will happen.

    I see a lot of spreadsheets where sheet B is supposed to auto-update based on data in sheet A but someone has replaced one or two links with typed values, invalidating all the results. While it could be possible to have a setup where typed values in sheet B also update sheet A it would be very hard to do without creating an infinite loop (A updates B which updates A which...). Unless someone else knows better?

    I recommend you should have just one place where data is updated and stick with it.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I agree with iansavell. It is not worth the effort trying to keep updating 2 separate places. The way that I can imagine doing it would be to NOT use any formulas or links, only values and have a table of which cells from each sheet are supposed to match which cells of the other sheet and then when either was changed, a macro would run, look up its "link" and change that cell.

    [Some issues: UNDO would be disabled, and any sorting you would want to do, would require that the link table be updated

    You could get around the "link table" if each entry on the rows had a unique key that was present in each set so that when a change were made the unique key could be used to find the appropriate row in the other set and then some "column" mapping to see which column of one sheet associated with the other]

    Steve

  4. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,494
    Thanks
    28
    Thanked 171 Times in 167 Posts
    You would also need to be careful to accomodate the different ways cells could be changed.
    For example, more than one cell can be changed at one time by using a 'copy and paste', and you could 'blank' a whole chunk of cells by selecting a range and hitting the [Delete] key.
    It is possible to use the worksheet change event to detect changes, determine whether the cell(s) that have been changed are relevant to the synchronising requirement, and implement the required changes on the other sheet (turning Events Off whilst making these changes to avoid the infinite loop referred to by iansavell etc).
    It can be complicated to do so you need to be sure that you need to do it i.e. there isn't a simpler solution.

    zeddy

Posting Permissions

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