+ Reply to Thread
Results 1 to 13 of 13

drop down menus, where the choice populates cost in corresponding cell on same row

  1. #1
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    drop down menus, where the choice populates cost in corresponding cell on same row

    I wanted to add my products/programs in a drop down in each cell in column E. and depending on the choice, the price would fill automatically in column G...
    is it possible?
    this would help me prevent the user from creating typo`s.


    drop down menus.JPG
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,801

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    Use a lookup table. Say in columns Q:R

    Q
    R
    1
    program 1
    3000
    2
    program 2
    2000
    3
    program 3
    15000
    4
    program 4
    5000
    5
    program 5
    1000

    • Select cells in column E you want the drop downs in.
    • Click Data > Data tools group > Data validation > Data Validation ... > Allow: List > Source: select the Programs in column Q > OK.
    • In G2 enter and fill down.
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    • Try changing the drop down selections and see how the results change.


    Please let me know how it goes.
    Dave

  3. #3
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    Dave thankyou I will test and get back this evening, kind regards.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,801

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    You are welcome.

    Looking forward to it.

  5. #5
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    Dave that works very well thank you,

    Am I able to place the look up tables on a separate sheet and lock that that sheet so users cannot see those look up tables?
    Or at least if I place the look up tables on another sheet what formula would reference them?

    Drop downs 2.JPG

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,801

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    On another sheet? Yes.

    About locking the sheet I know nothing. Never had occasion to use them, and I about to sign off for the week end.

    In the meantime try applying what you've learned so far on an empty workbook.

    I'll check back.

  7. #7
    Forum Contributor jomaor1's Avatar
    Join Date
    10-08-2018
    Location
    Brasil
    MS-Off Ver
    Excel 2016
    Posts
    175

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    I have a quicker solution,

    When you create the Data Validation you don't need to reference the List to a Range
    Instead, do this:

    on your "list" field, type

    program1,program2,program3,program4,program5

    Cheers.
    John.

    "I excel at jumping to conclusions"

  8. #8
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    thanks Dave!.

    i`ll wait for your answer next week.

    I have tried the list on another sheet, and I believe my input values are correct, but i`m getting N/A for the result.

    Attachment 628392

    Attachment 628393

    have attached the sheet here.

  9. #9
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    spreadsheet here re previous reply,,
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    thanks Jomaor, very useful for short lists!.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,801

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    You need to include the sheet name in the VLOOKUP.

    Like this

    =VLOOKUP(E2,lists!$B$2:$C$6,2,FALSE)

    And I got it to work with sheet 'lists' hidden.
    Last edited by FlameRetired; 06-15-2019 at 11:13 PM.

  12. #12
    Forum Contributor
    Join Date
    04-01-2015
    Location
    Sydney Australia
    MS-Off Ver
    Office 365 latest
    Posts
    171

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    awesome thanks Dave!
    works for me.

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,801

    Re: drop down menus, where the choice populates cost in corresponding cell on same row

    Glad to hear it. You are welcome. Thank you for the feedback, added rep and marking your thread 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. Multiple Time Stamps depending on choice in Drop Down List Choice
    By insayah in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-25-2022, 09:53 AM
  2. Need Drop-Down Selection to Auto-Populates Data in Adjacent Cell
    By Prodetik in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-16-2019, 08:25 PM
  3. Replies: 3
    Last Post: 09-08-2016, 03:27 AM
  4. Drop down list that auto populates appropriate cell
    By JKropetz in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-15-2015, 02:17 PM
  5. Replies: 4
    Last Post: 02-18-2014, 07:58 PM
  6. Replies: 7
    Last Post: 02-06-2014, 07:40 AM
  7. Replies: 3
    Last Post: 09-17-2013, 10:45 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