Solved Copy cell info based on drop list choice

February 17, 2014 at 13:00:44
Specs: Windows 7
Hi. I know how to play around with basics functions in excel, but there is one thing I'd like to do and cannot find how to do it.

I have a list of Equipments (2nd sheet model/year/hours/prices/etc...) that gives me rounded value per year/model, but it's quite a large list. I'd like to set up (at the top of first sheet) a box where I can select from two drop list Models and years, and the rounded value would appear in a clean easy to read manner.

message edited by Shallak

See More: Copy cell info based on drop list choice

Report •

February 17, 2014 at 15:10:45
✔ Best Answer
Since we don't really know how your data is laid out, it's kind of hard to give a specific answer. Maybe this generic concept will point you in the right direction.

Let's say this is your table on Sheet2:

     A      B         C  
1  Year   Model     Value
2  2013  Model A     100 
3  2013  Model T     200

First, insert a new Column A (often called a Helper Column) then put this formula in A1 and drag it down to the bottom of your list:


You should have a table that looks like this:

         A          B        C           D   
1  YearModel       Year    Model       Value
2  2013Model A     2013    Model A      100 
3  2013Model T     2013    Model T      200

You may Hide Column A if you wish.

Now, back in Sheet1...

1 - Create a Drop Down For the Year in A1
2 - Create a Drop Down for the Model in B1
3 - Enter this formula in C1:


This will VLOOKUP the combination of the values from the 2 dropdowns in the first column (A) of the table on Sheet2 and return the value in the 4th Column (the Value)

You may need to clean this up a bit to conceal any #N/A errors if a combination doesn't exist, but I'll leave that up to you.

Let us know how that works out for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

February 18, 2014 at 09:36:57
This is perfect! Thank you :D

Report •

Related Solutions

Ask Question