Search code examples
excelvalidationdropdownlistfor

Pulling information from 1 worksheet to another, based on drop down list selection

I am trying to get a spreadsheet to auto populate rates for products, based on the size of the order, which are chosen from a drop-down menu.

I am using 2 workseets - Sheet 1 has a data validation drop down list of the Order Sizes (0-49, 50-99, 100+), and another column of Products, where I would like the rates to come across. Sheet 2 has 3 tables - One table per Order Size, containing the products' rates for that size order (Table 1 = 0-49, Table 2 = 50-99, Table 3 = 100+).

I can't figure out how to get the rates from the 3 tables (on Sheet 2) to pull across and auto-populate in the Products column (on Sheet 1), once I've chosen the Order Size from the drop down list (on Sheet 1).

I hope that makes sense. Unfortunately I'm too new to this site, so it won't let me post images or files

I don't really understand Macros or VBA, so please dumb it down for me :-) Thank you!

Solution

  • You can accomplish this without using any VBA by making use of the IF and VLOOKUP functions. I'm assuming your data looks like this:

    enter image description here

    enter image description here

    The formula in B8 is: 

    =IF(B2="0-49",VLOOKUP(B5,Sheet2!A3:B5,2,FALSE),IF(B2="50-99",VLOOKUP(B5,Sheet2!A8:B10,2,FALSE),IF(B2="100+",VLOOKUP(B5,Sheet2!A13:B15,2,FALSE),"Enter a valid order size selection")))

    Basically the IF statements tell it which table to look in, based on the selected order size, and the VLOOKUP statements do the looking up based on the selected product. An IF statement takes the form of:

    IF(logical_test, [value_if_true], [value_if_false])

    So, this first checks if the value of B2 is "0-49." If it is, it performs the lookup in the small order table. If it's not, it goes on to check if the value is "50-99." Etc. Then, at the end, if the value isn't any of the options, it prompts the user to enter a valid order size.

    See:

    http://office.microsoft.com/en-us/excel-help/if-function-HP010342586.aspx

    http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx