+ Reply to Thread
Results 1 to 8 of 8

Populating information from a "Master List" into other sheets

  1. #1
    Registered User
    Join Date
    03-04-2015
    Location
    Mountain View, CA
    MS-Off Ver
    2010
    Posts
    2

    Populating information from a "Master List" into other sheets

    Hi all,

    I am trying to create an equipment tracking list. I've made up a mock form as the attachment to show you the idea of how things are sorted. The first sheet acts as the Master List and is a running log where all updates are made.

    What I'm trying to do is sort "Active" and "Inactive" equipment into separate sheets without needing to manually copy information to their respective lists. Basically, I'd like to make a dynamic system such that the file will populate all the information on the next available row in these sheets.

    For example, if a piece of equipment changes from "Active" to "Inactive", it will automatically add the equipment's information to the "Inactive" list and remove it from the "Active" one. Furthermore, if a piece of equipment is added to the Master List and is marked as "Active", it will add the information to the "Active" list.

    The other thing I'm trying to do (kind of a stretch) is a calibration tracking list. The idea I have is that if a date is given for the calibration due date, it would recognize that as an equipment that requires calibration and would add that equipment's information to the "Calibration" sheet.

    It's been a while since I've done any major Excel work, so I'm extremely rusty with using formulas. I have some experience with VBA, but I'd like to avoid it if possible due to limits on the scope of the project. From what I know using excel, this is leaning towards the VBA side. Any help or suggestions would be appreciated.

    Thanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Populating information from a "Master List" into other sheets

    Try this ARRAY formula in Act Equip A2, copied down and across...
    =IFERROR(INDEX('Equipment Master List'!A$2:A$31,SMALL(IF('Equipment Master List'!$G$2:$G$31="Active",ROW('Equipment Master List'!$A$2:$A$31)-1),ROWS('Equipment Master List'!$A$1:A1))),"")

    You can adjust this by changing the BOLDED part

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    You may need to fiddle with the formatting to get things shown as you need them to be
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Populating information from a "Master List" into other sheets

    It can be done with formulae, although it might become a bit cumbersome if you have thousands of rows of equipment. I'll put something together for you to show how it can be done.

    Pete

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Populating information from a "Master List" into other sheets

    If I understand your 2nd part correctly, try this in req Cali sheet A2, copied down and across....
    =IFERROR(INDEX('Equipment Master List'!A$2:A$31,SMALL(IF(('Equipment Master List'!$H$2:$H$31<=TODAY())*('Equipment Master List'!$H$2:$H$31<>""),ROW('Equipment Master List'!$A$2:$A$31)-1),ROWS('Equipment Master List'!$A$1:A1))),"")

    Also an ARRAY formula

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Populating information from a "Master List" into other sheets

    The attached file shows how you can do this without using array formulae. I've set up 3 helper columns in the master sheet, and inserted a new column A in the other sheets to act as helpers - the hyphens indicate how far the formulae have been copied down.

    Hope this helps.

    Pete
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-04-2015
    Location
    Mountain View, CA
    MS-Off Ver
    2010
    Posts
    2

    Re: Populating information from a "Master List" into other sheets

    Awesome! The formula works great. Thanks for the help guys.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Populating information from a "Master List" into other sheets

    Happy to help, thanks for the feedback

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Populating information from a "Master List" into other sheets

    Thanks for the rep - if that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

    Pete

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Update list of tasks in several sheets based on "Master-list" in sheet A
    By Jacob2010 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-02-2015, 06:22 AM
  2. Create Separate Sheets from "Master List"
    By krista_o in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2014, 05:37 PM
  3. [SOLVED] Working with multiple sheets, need the "Master Sheet" to collect all its data
    By switzd0d in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-20-2014, 10:51 AM
  4. Copy Specific Columns (Named ones) from different sheets to a new "master" sheet
    By aribe88 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-11-2013, 04:10 PM
  5. creation of "master sheets" or "template"?
    By CJPB in forum Excel General
    Replies: 2
    Last Post: 12-15-2010, 12:24 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1