+ Reply to Thread
Results 1 to 13 of 13

Dropdown to populate data from master sheet

  1. #1
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Post Dropdown to populate data from master sheet

    I need some help with getting two dropdowns to populate data on my master sheert.

    So I'd like to be able to select the Year then Month and the data populate for the date selected below. So I select 2019, then Sept, I would like to see the budget items for Sept 2019, and then if I select Oct, Items populate for Oct, and so forth. The data sheet has the due date (each item occures only once per month) and a makeshift calendar with "X" meaning the item has been paid.

    All data is stored in the data sheet, but I don't know how to get it to work. It could be my layout is mess me up with figuring this out, but I am at a lose here. Any help would be greatly apperciated.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    899

    Re: Dropdown to populate data from master sheet

    Try these array formulas (paste, then confirm by pressing Ctrl, Shift and Enter keys simultaneously):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For next two columns, change the first range (Data!$B$2:$B$100) accordingly.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Good luck!
    Last edited by Estevaoba; 09-20-2019 at 06:49 PM.

  3. #3
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Re: Dropdown to populate data from master sheet

    I can't get the arrays to paste and functions correctly. I end up with just the formulas you provided. Would you be able to modify the first attachment and repost? Maybe I can see where I am going wrong. Thanks again!

  4. #4
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    899

    Re: Dropdown to populate data from master sheet

    I guess it is a language related issue, so to get around it, I made your month list on column BC at Data tab with this formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Please check attachment.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Re: Dropdown to populate data from master sheet

    Awesome, thanks for the help. However, I think I didn't explain well what I was trying to achieve. The month and year selector (which I need to make prettier), I would like the Budget sheet to reflect the status of payment with the Data sheet and "X" markings. So if Terran has an "X" for Sep 2019 (S4 on the Data sheet) then on the Budget sheet when 2019 Sep is selected, the Bill Name, Amount and Due date are populated. Anyway we can get that to work? Again I really apperciate your help.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    899

    Re: Dropdown to populate data from master sheet

    I understand you want to list only those rows marked with an X, so please try these array formulas. Make sure to array enter - after pasting, press Ctrl+Shift+Enter.
    If you do it right, you'll see the formula inside curly braces { } in the formula bar.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Change range accordingly for next two columns.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Re: Dropdown to populate data from master sheet

    Sweet! I have one last question for you, I'd like to add a scroll bar, could you assist in that? I expanded the data sheet to account for paid inidcator and amount (since that could change month to month). I tried to adjust for formula, but don't fully understand it. Would you please correct for me, that should be it after that. Thanks again.
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    899

    Re: Dropdown to populate data from master sheet

    You're welcome. I'm glad to help.

    I made minor adjustments in the above formulas to accomodate the added columns in Data tab in those OFFSET/MATCH ranges.
    Also, I put those formulas in the Data tab, and used your OFFSET formula to display results in your scroll bar range.

    I hope it works.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Re: Dropdown to populate data from master sheet

    Alright my friend, I need your assistance one last time for this. I was copy to copy and modify the formula for the "Investments" on the Data sheet, however, I can't seem to get the formula to populate the vaules from the "calandar" sections. Data tab HA2 is where the formula is and A101 is where the Names are and K100:AH125 is where the data is stored. Once I have "2019" coded right, I plan on expanding through 2025 (which I have is as easy as just copying the data calandar over. Again thank you so much for your help.
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    899

    Re: Dropdown to populate data from master sheet

    You're welcome.

    You had misplaced the reference cell in the OFFSET function. Instead of I1, it should be I101 for the Investment data section.

    Please check attachment.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Re: Dropdown to populate data from master sheet

    Alright, I saw this addition that I'd like to make, and I have spent all night trying to figure this out. As you can tell I know nothing about arrays or how to reference them correctly. If you could and have a sec, this additional excel file has a "reminder" that I'd like to incorporate on the "register" sheet. I've done my best to try and copy and paste and modify the formula to reference my "data" sheet and associated fields, but no success. Would you kindly help, yet again?
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor
    Join Date
    06-05-2017
    Location
    Brazil
    MS-Off Ver
    Microsoft 365
    Posts
    899

    Re: Dropdown to populate data from master sheet

    I'm afraid you have now lost me completely.
    I'll help you on if I can, no problem, but if your original question has been resolved, I guess you'd better mark this thread SOLVED and start a new one.

    And when you do, please mock-up desired results manually.

  13. #13
    Registered User
    Join Date
    06-21-2019
    Location
    Topeka
    MS-Off Ver
    2016/Office 365
    Posts
    17

    Re: Dropdown to populate data from master sheet

    My bad, created new thread: #post5200775

    marking this as solved, thank you so much for your help.

+ 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. Want to auto populate an excel sheet from a master sheet of data
    By M>E> in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2014, 03:34 PM
  2. [SOLVED] Using a dropdown menu to change data on a master sheet
    By elmerg in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-22-2013, 05:24 AM
  3. [SOLVED] Populate master sheet from data on multiple sheets
    By jhammond10 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-05-2013, 01:36 PM
  4. [SOLVED] Populate data on another sheet based on dropdown criteria from the first sheet
    By ARod0726 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-19-2013, 01:36 PM
  5. Replies: 0
    Last Post: 04-16-2013, 07:29 AM
  6. [SOLVED] Populate Master Sheet with data from other sheets in workbook...
    By DMA-Pacific in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-18-2013, 01:21 AM
  7. [SOLVED] Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet data
    By EMLalan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-07-2012, 09:46 AM
  8. Populate Data from Master Sheet to Multiple Worksheets
    By redheadedstepchild in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2012, 04:50 PM

Tags for this Thread

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