Dropdown menu seperate value help

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:26 PM GMT

Question

I have a dropdown menu on sheet one, that pulls data from sheet 2. The dropdown select data are words, but the problem I am having is creating a monetary value to each individual item that will be added together in other columns to get a total.

On sheet 1 I have a colum named new Install. The data is being pulled from sheet two with the following formula, "=NewInstall" in the Data Validation as Allow List. On sheet 2 I have the following: A (named NewInstall) B (named InstallValues No $0.00 Yes $70.0 Yes + 1 $85.00 Yes + 2 $100.00 Yes + 3 $115.00 Yes + 4 $130.00 Yes + 5 $145.00 Yes + 6 $160.00 Yes + 7 $175.00 Yes + 8 $190.00 Yes + 9 $205.00 Yes + 10 $220.00 What I need to do is when I select one of the options from the dropdown on sheet 1 is to have that option displayed but have the monetary value associated with it (monetary value doesn't need to be displayed here) I will then have the monetary value from several similar columns added and totaled in another column.

I hope that I have explained this well enough for you.

Answer

Screenshot


To have the monetary value associated with the value in the dropdown (data validation) shown in a cell, simply VLOOKUP the cell of the dropdown to the data in sheet 2.

=VLOOKUP(A1,Sheet2!A2:B5,2,0)

where A1 is the dropdown A2:B5 is the table_array for VLOOKUP

See the attached screenshot for an example.

I hope that this answers your question.

Advertisement

©2020 eLuminary LLC. All rights reserved.