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.

Excel formula link between sheets

Discussion in 'Business Applications' started by Madrex, Dec 4, 2011.

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

    Madrex Thread Starter

    Joined:
    Dec 4, 2011
    Messages:
    3
    I have a workbook containing a master sheet (sheet 1), and several other sheets (sheets 2 to 7) which I want to populate with colour highlighted cells that automatically appear in the corresponding cells in the master sheet.
    Is there a formula/function that can do this?
    Thanks
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi welcome to the forum, Yes there is but it all depends on what and you realy want and how the data is in the sheets
    If they're all in equivalent cells you can enter a formula in let's say Cell A1 that adds all the values from all the cells AI of each sheet:

    A1=Sheet2!A1+Sheet3!A1+ etc...

    You forgot to mention what version of Excel you're using, that is also important.
    A sample of what you have and what you want would help give us a better idea
     
  3. Madrex

    Madrex Thread Starter

    Joined:
    Dec 4, 2011
    Messages:
    3
    I'm using Excel 2007.

    I've attached a sample workbook of what I mean (its not the one I'm actually using). Sheets 2, 3, 4 and 5 (named 'Tom' 'Dick', 'Harry' and 'Bob') are each populated with a name, and a grid of red green or yellow coloured cells, and that data can change. The data is different in each sheet, although the range of cells is the same in each sheet. As the cell grid is populated/changed, I want that information to automatically populate sheet 1 'Master' in the same format, with the information for 'Tom' first, then 'Dick' beneath, and so on. I don't want to copy and paste, as the actual workbook I'm using contains a large amount of frequently changing data. So basically, I just want sheet 1 to be read only.

    Hope that makes sense. Thanks for your help.
     

    Attached Files:

  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay, I'll look at it, it's almost midnight here so it won't be until after work tomorrow.
    Good-night (at least for me now) I don't know your time zone
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Maybe this works for you.
    The macro to run is 'UpdateActivities'

    I did not choose to delete previous data in the Master sheet but this you could include too to make sure you refresh all and only show those where there is an activity.

    If more months are added it will take these with it.
    I included some explanatory info in the vba code, it's really quite straight-forward, nothing magic ;)
     

    Attached Files:

  6. Madrex

    Madrex Thread Starter

    Joined:
    Dec 4, 2011
    Messages:
    3
    Thanks for your help, I think this can work for me. I need to play around with it a bit and try it on my actual workbook, and also make sure I understand your notes.

    Thanks again, this is really helpful!
     
  7. 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/1029688

  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