Data validation (Drop down) to auto-populate column with formula

1
votes

I'm trying to make an excel sheet a lot smaller and easier to handle. What i'm trying to do is have a drop down auto-populate a whole column.

My original sheet looks like:

Item Name(A1) Item Code(B1) Price1(C1) Price2(D1) Price3(E1) Price4(F1)

My goal is to look like:

Item Name(A1) Item Code(B1) Price Drop Down(C1)

I also want to be able to delete an item in Column A and still be able to have the correct values. My VLOOKUP only uses 1 column and wont change (of course) when I switch to a different price set.

1
excelvlookup

1 Answers

0
votes

You could try something like this:

Copy your original table to another sheet (sheet2), so you can use it as the lookup table. In the original sheet delete the last 4 columns (C:F). Next create a dropdownmenu in cell C1, with items {price1 ,price2 ,price3 ,price4}. Put the following formula in cell C2 and copy down.

=VLOOKUP(A2,Sheet2!A:F,RIGHT($C$1,1)+2,FALSE)

The last parameter [FALSE] is necessary when the item names in the lookuptable are not in alphabetical order.