1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Excel Problem

Discussion in 'Software' started by Rob1234, Sep 17, 2008.

  1. Rob1234

    Rob1234 Megabyte Poster

    784
    24
    69
    I want to link a worksheet in one workbook to a worksheet in another. So they both contain the same worksheet and if you update one worksheet it automatically updates the other worksheet.

    I have managed to make it so when you update one worksheet it updates the other but if you update the other it does not update the other worksheet (if you know what I mean)

    Any ideas?
     
    Certifications: A few.
  2. Qs

    Qs Semi-Honorary Member Gold Member

    3,081
    70
    171
    Yep:-

    • Hold CTRL on your keyboard and click all of the available worksheets using their appropriate tabs at the bottom left of the screen. This will 'group' the worksheets.

    • Do your changes

    • It should reflect on all selected worksheets.

    Hope this helps. :)

    Qs
     
    Certifications: MCT, MCSE: Private Cloud, MCSA (2008), MCITP: EA, MCITP: SA, MCSE: 2003, MCSA: 2003, MCITP: EDA7, MCITP: EDST7, MCITP: EST Vista, MCTS: Exh 2010, MCTS:ServerVirt, MCTS: SCCM07 & SCCM2012, MCTS: SCOM07, MCTS: Win7Conf, MCTS: VistaConf, MCDST, MCP, MBCS, HND: Applied IT, ITIL v3: Foundation, CCA
  3. Hades

    Hades Nibble Poster

    90
    3
    0
    Is it like Q said?

    Or are you wanting to send data each way? as in you update worksheet 1 in workbook A and it updates workseet 1 in Workbook B then if you update worksheet 1 in workgroup B it updates worksheet 1 in Workbook A??

    Just trying to get my head round this lol
     
    Certifications: City & Guilds Diploma in ICT level 2+3
  4. Qs

    Qs Semi-Honorary Member Gold Member

    3,081
    70
    171
    Yeah the OP made my brain hurt too.

    My solution works if all the worksheets are in the same workbook.

    If the worksheets are in separate workbooks I'd assume you'd need to implement a macro to achieve the desired results.

    Qs
     
    Certifications: MCT, MCSE: Private Cloud, MCSA (2008), MCITP: EA, MCITP: SA, MCSE: 2003, MCSA: 2003, MCITP: EDA7, MCITP: EDST7, MCITP: EST Vista, MCTS: Exh 2010, MCTS:ServerVirt, MCTS: SCCM07 & SCCM2012, MCTS: SCOM07, MCTS: Win7Conf, MCTS: VistaConf, MCDST, MCP, MBCS, HND: Applied IT, ITIL v3: Foundation, CCA
  5. Rob1234

    Rob1234 Megabyte Poster

    784
    24
    69
    Yes thats what I want, sorry I did not explain it better, any ideas?
     
    Certifications: A few.
  6. Hades

    Hades Nibble Poster

    90
    3
    0
    Right... ok

    The way I remember it and I had to google this to be sure, in this scenario Workbook A would become "the Source" and workbook B is "the dependent". Now Im not sure if you can have them both as "source".

    I think there is a way round this but it will be even mind blowing than it is in the minute... I'll have a play and see if I can get this working... will require more coffee and sugar first though
     
    Certifications: City & Guilds Diploma in ICT level 2+3
  7. Qs

    Qs Semi-Honorary Member Gold Member

    3,081
    70
    171
    Could you not just copy the worksheets from your second workbook to your first workbook as a separate worksheet (confused yet? :p)

    Then you could use my solution to update the data in both. Once you're finished copy all of the desired data back to a separate workbook.

    This is obviously dependant upon what you want to update (cell specific locations etc etc).

    Qs
     
    Certifications: MCT, MCSE: Private Cloud, MCSA (2008), MCITP: EA, MCITP: SA, MCSE: 2003, MCSA: 2003, MCITP: EDA7, MCITP: EDST7, MCITP: EST Vista, MCTS: Exh 2010, MCTS:ServerVirt, MCTS: SCCM07 & SCCM2012, MCTS: SCOM07, MCTS: Win7Conf, MCTS: VistaConf, MCDST, MCP, MBCS, HND: Applied IT, ITIL v3: Foundation, CCA
  8. Rob1234

    Rob1234 Megabyte Poster

    784
    24
    69
    not really as it is for a user who is creating a works schedule and needs the two worksheets to haev the same data, so when they type it in worksheet A workbook 1, Worksheet A Workbook 2 automaically gets updated and vice versa.
     
    Certifications: A few.
  9. Rob1234

    Rob1234 Megabyte Poster

    784
    24
    69
    That is where I am stuck I can only make one the source which can update the other but the dependent one cannot update the source one.

    Also Worksheet 2 is password protected which I think means it can not be a "source" one??
     
    Certifications: A few.
  10. Fergal1982

    Fergal1982 Petabyte Poster

    4,196
    171
    211
    I dont think both could be 'source'. Otherwise you end up with a perpetual cycle, where the worksheets are passing data back and forth, back and forth. ie:

    worksheet 1 is updates A1 = 100
    Update sent to Worksheet 2 A1 = 100
    Worksheet 2 sees that its been updated and sends the update to worksheet 1 A1 = 100
    ...

    Do you see how this is going to go? Thats just for one change, imagine you are constantly updating loads of cells in both sheets. Imagine how quickly your pc is going to crash. Now imaging those two sheets are on different network locations, how quickly is the network going to die with the increasing load?
     
    Certifications: ITIL Foundation; MCTS: Visual Studio Team Foundation Server 2010, Administration
    WIP: None at present
  11. Qs

    Qs Semi-Honorary Member Gold Member

    3,081
    70
    171
    Presumably the two workbooks aren't identical right?

    I'm assuming that suggesting "copy/paste" isn't a valid answer? :p

    Qs
     
    Certifications: MCT, MCSE: Private Cloud, MCSA (2008), MCITP: EA, MCITP: SA, MCSE: 2003, MCSA: 2003, MCITP: EDA7, MCITP: EDST7, MCITP: EST Vista, MCTS: Exh 2010, MCTS:ServerVirt, MCTS: SCCM07 & SCCM2012, MCTS: SCOM07, MCTS: Win7Conf, MCTS: VistaConf, MCDST, MCP, MBCS, HND: Applied IT, ITIL v3: Foundation, CCA
  12. Hades

    Hades Nibble Poster

    90
    3
    0
    Yeah I totally overlooked the fact that this will rapidly crash the machine depending on the scale of changes :oops:

    And having 1 worksheet password protected is going to throw a major spanner in the works :eek:
     
    Certifications: City & Guilds Diploma in ICT level 2+3

Share This Page

Loading...