+ Reply to Thread
Results 1 to 18 of 18

how to auto populate one cell based on the selections of TWO drop downs?

  1. #1
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    how to auto populate one cell based on the selections of TWO drop downs?

    Hi All.
    I am a newbie at Excel, so please forgive my lack of knowledge of terminology. I have searched for 3 days now but can't find anything on my problem.
    I have a sheet which has 2 drop downs.
    One is to select the trailer, the other is to select the tent.
    The price varies depending on which tent goes on which trailer.
    I am hoping there is a way to have the price fill in automatically, based on which selections are made in the drop downs regarding tent and trailer.
    Hopefully this is simple??
    Any help greatly appreciated.
    Thanks!!

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Use Vlookup for that.

    In Vlookup you can reference to the cell with the dropdown.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Thanks for that. I have used VLOOKUP to populate a cell based on the selection of 1 drop down, but how do I use it to populate a cell based on the selection of 2 drop downs?
    I have:
    =VLOOKUP(b23,TentPrice,2,FALSE)

    what is an example of modifying this formula to 'read' from 2 drop downs?

    Thanks again, I appreciate your help.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Post an small excel example, without confidentional information.

  5. #5
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Thanks again. I have attached a sample of my sheet.
    You can see on the invoice sheet that the user can select the camper trailer, and then the tent. The combinations and prices are on the sheet 'trailers & tents combo'. The price on the invoice sheet was working but I just deleted the sheet for trailer prices as it was not going to work.
    Thanks for your assistance, greatly appreciated.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    @pigalet

    the defined formalus are deleted!!

    that's why you don't get the right anwer.

    but in order to your question.

    you have to combine the values (camper and tent) in 1 cell and make 1 table of it (instead of 2).

    then you can use VLookup to find the information (price).

  7. #7
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Hi again.

    Woops, told you I was a newbie.
    I have re attached the file, which should all work now, as far as I got it at least.
    On the invoice sheet, the selection on the tent model shows prices, but the prices need to change depending on which trailer is selected in the drop down box above it.
    I have 1 range, TentPrice, but don't think this is right (thats why I deleted it in the last file). I have set up another sheet with Trailer & Tents Combo which I think may be better.
    I need to know how to set it up so that, for example, if someone selected a Seeker Camper Trailer, with a ST12KSS tent, the price would show in H23 as $5295 (as shown in the Trailer & Tents Combo).

    Kind regards.
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    See the attached file.

    I changed the used defined names and datavalidation.

    I combined the camper and the tents.

    Then i used the combined list to find the value with the VLookup formula.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    That is perfect!! You are amazing - thanks so much.
    I just have one more question, is there a way to make the choices in the drop down list show only once? ATM seeker shows as many times as it is in the table, and the tent models repeat the same. I am trying to keep the list short and as user friendly as possible, with each option showing just once. I think this will have to do with how I lay out the table?
    Any suggestions?
    Thanks again.

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Basicly your looking for a multiple-linked-dropdown-list.

    See links on this item:

    Create multiple linked dropdown list in excel
    http://helpdeskgeek.com/office-tips/...ists-in-excel/
    http://thefinch.wordpress.com/2008/0...datavalidatie/
    http://www.contextures.com/xlDataVal02.html
    http://www.snb-vba.eu/VBA_Afhankelijke_validatie.html also in Englisch

  11. #11
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Thanks again, this will be my reading today and tonight.
    Kind regards.

  12. #12
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    I'm back!
    Have read about dependent lists, got 2 up in my sheet. They are at B33 (trailer) and B34 (tent). These lists are made from the data in Camper Trailer Models sheet.
    Now I'm back to where I began, getting the price to show up in (for this example)H34 dependent on what is selected from the lists just mentioned.
    Can anyone help me understand this please? I have tried to go with oeldere's help before but I don't really understand what he did so I cant see why its not working. Please forgive my neewbieness!!
    I have attached a NEW sheet containing the dependent lists.
    Any help will be so appreciated. Thanks again.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Can anyone tell me what the last 2 numbers stand for in this:
    =VLOOKUP(B22&" "&B23,'Trailers & Tents Combo'!C$2:D$27,2,0)

    The '2' and the '0'?
    columns? rows? if so, is '0' a column/row?

    Thanks very much.

  14. #14
    Registered User
    Join Date
    02-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Hi

    The 2 is the number of columns you are looking at after the search criteria.
    the 0 which means FALSE gives you an exact match to given criteria
    if the 0 was a 1 this means TRUE and gives the closest answer to given criteria
    the citeria is the item you are searching for in the lookup

    hope this helps

  15. #15
    Registered User
    Join Date
    02-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Hi

    The 2 is the number of columns you are looking at after the search criteria.
    the 0 which means FALSE gives you an exact match to given criteria
    if the 0 was a 1 this means TRUE and gives the closest answer to given criteria
    the citeria is the item you are searching for in the lookup

    hope this helps

  16. #16
    Registered User
    Join Date
    02-04-2013
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Thank you!! I appreciate you taking the time to explain this to me. Simple once pointed out. I'm now off to read up on HLOOKUP's.

  17. #17
    Registered User
    Join Date
    02-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Hi
    You just need to remember Vlookup look down a table and a hlook up looks across a table
    have attatched a spreadsheet that shows how each lookup works if you look at shhet 2 you can play with it and see how it works
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    02-04-2013
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: how to auto populate one cell based on the selections of TWO drop downs?

    Hi
    Sorry forgot to add sheet 2 data is for vlookup and sheet 3 data is for hlookup

    me bad

    hope it helps

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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