Forum Discussion
Data validation with multiple columns
Hello all
I work for a medical centre in australia and do all the ordering, I use an excel spreadsheet to enter all the data but it is very time consuming, I want to use data validation from a drop down list to automatically be able to search the list with the product and price, but I cant figure out how to use this for multiple columns. To clarify I want to select a product and the price attached to that product so it will automatically go into the spreadsheet from my list. I hope that makes sense, any help would be appreciated. Thanks
4 Replies
I would like to add to Hans' solution that you need to create a named range for the first column in the Product table, and point the data validation list to that named range. Then it will automatically expand with the Product table when it is NOT in the same sheet as the cells with data validation. See attached.
Thanks, Riny!
As Hans shown in his answer, how to pull price by selecting name of item, is it what you want or something else?
Additionally INDEX/MATCH will also work in all version of Excel.
=INDEX(G:G,MATCH(C5,F:F,0))I assume that you have a list of products with their prices. It works best if this list is a table (Insert tab of the ribbon > Table to convert a range to a table):
On the sheet where you want to enter the products you order, you can use data validation of type list with the first column of the table as source.
Again, this works best in a table. You can use XLOOKUP (or in older versions, VLOOKUP) to retrieve the price for a selected product.
See the attached sample workbook.
