+ Reply to Thread
Results 1 to 4 of 4

How to populate other cells based on Drop Down Value (plus a couple other questions!)

  1. #1
    Forum Contributor
    Join Date
    11-13-2013
    Location
    California
    MS-Off Ver
    Excel 365 Apps for enterprise
    Posts
    180

    How to populate other cells based on Drop Down Value (plus a couple other questions!)

    Hello Hive Mind!

    I recently inherited a spreadsheet that is not very useful and I'm trying to find a way to make it more dynamic and easier to use. I've attached a sample.

    The main question is:

    How do I populate other cells based on the value selected from a drop down?

    For example...

    On Sheet OT...

    I would like for the "Name" to be a drop down. Based on what name is selected, I would like columns A, B, and J to grab the corresponding values from the table "Employees" on the Drop Downs sheet. (This is where the Name list is coming from).

    Knowing how to do that, solves a couple of the other areas where I would want to do something similar (i.e. on the Shift tab, the drop down would be on the Earn ID and the Rate would populate)

    _______________________________
    Another question I have...

    Is there a way to automatically use a formula based on which item is selected from a drop down? Would that need to be set up similar to my first question? For example, on the OT spreadsheet, if the Earn ID is OTPR then the formula in column M should be =SUM((([@[Monthly Base]]+[@Shift]+[@Stipend])/173.333)*1.5) but is the Earn ID is OTST it should be =SUM((([@[Monthly Base]]+[@Shift]+[@Stipend])/173.333)*1)

    _______________________________
    My last question...

    On the stipends sheet, I have four columns (D,E,F,G) where there is a drop down. Each item in the drop down has a dollar value on the drop down sheet. Is there a way to come up with a total based on the drop down selections in those 4 columns?



    I think that's it... I really appreciate everyone's help!!!
    Attached Files Attached Files

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to populate other cells based on Drop Down Value (plus a couple other questions!)

    for DV Drop Down on OT

    after changing DV you have to select Refresh from the context menu on the green table
    Attached Files Attached Files
    Last edited by sandy666; 09-29-2022 at 08:17 PM.

  3. #3
    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
    53,047

    Re: How to populate other cells based on Drop Down Value (plus a couple other questions!)

    Just to add in, There is nothing magical about a drop down (data validation), it acts in the exact same way as if you had entered the data manually, and excel formulas only see the data in the cell as if it had been entered manually.
    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

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,687

    Re: How to populate other cells based on Drop Down Value (plus a couple other questions!)

    Try

    in "OT"

    im M2

    =SUM((([@[Monthly Base]]+[@Shift]+[@Stipend])/173.333)*IF([@[Earn ID]]="OTPR",1.5,1))

    in "STIPENDS"

    in N2

    =SUMPRODUCT((Stipend_Type[Amount])*(Stipend_Type[Stipend Type]=Stipends[@[Stipend Name]:[Special Assignment 3]]))
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

+ 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. [SOLVED] Populate Multiple Cells based off one drop down
    By dareeldill in forum Excel General
    Replies: 7
    Last Post: 10-22-2021, 08:27 AM
  2. Replies: 3
    Last Post: 01-31-2013, 06:22 PM
  3. Populate cells based on the selection from a drop down box
    By bartow in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-13-2011, 03:27 PM
  4. A couple of questions
    By kingdt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2007, 07:41 AM
  5. a couple of questions
    By moshmoshon in forum Excel General
    Replies: 2
    Last Post: 08-31-2007, 05:31 PM
  6. Just a couple questions....
    By Punk0Rama in forum Excel General
    Replies: 1
    Last Post: 09-10-2006, 11:35 AM
  7. [SOLVED] Couple more questions...
    By Poor microsoft user in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-27-2005, 02:47 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