We have a group planning to charter a sailboat as a vacation for 10 days. I'm trying to show the total costs of the trip. It's a simple spreadsheet listing many semi-fixed costs like food, beer, fuel, insurance, mooring fees, etc.

I know that Boat A costs 1000, Boat B costs 1500 and Boat C costs 2000. I want the user to be able to select from a drop-down Boat A, B, or C and have it affect the bottom-line sum.

How can I do that?

Currently, using Data Validation all I know how to do is have the values (1000, 1500, 2000) in the drop-down list. I want an end value to change automatically based on what the user picked (Boat A, Boat B, Boat C).

Update: 5/18/16 - Still not working 100% I have 6 boats listed yet 2 of the 6 do not display the correct value that is in the table. The table data is E2:F7
Boat1 5999
Boat2 6599
Boat3 4999
Boat4 5099
Boat5 6999
Boat6 6499
Cell B5 is my drop down list containing Boat1 to Boat6
Cell C5 is my formula to determine the cost of the boat selected divided by 3.
Cell C5 has the following formula =(VLOOKUP(B5,$E$2:$F$7,2))/3
It displays the correct value for all Boats except Boat1 & Boat2. For those is calculating based on a boat price of 6999...which Boat1 & 2 are not those prices.
Help!..so close


If you use the cell with the drop down in your calculations, the sum will change automatically.

If you prefer to have the drop down show text, you need to select the matching cost from some table somewhere - you could place the six cells "Boat 1", 1000, "Boat 2", 1500, "Boat 3", 2000 (in three rows) wherever you like (in a spot nobody looks, or a separate sheet, or right smack on your sheet).

Then use the formula =VLOOKUP(<cell with the dropdown>,TheTable,2,FALSE) to calculate the price for the selected boat, and use this in the further calculations.

Make sure to spell the texts in the drop down the same as in the table, or the formula won't find it.

  • Thank you!...as soon as I researched "tables" I figured out I needed to use $A$1:$B$3 in place of "TheTable" to indicate the upper left and lower right of the example table above....thank you! – Bryan Fitzgibbons May 17 '16 at 13:35
  • It didn't work 100%..i added more detail to original message. – Bryan Fitzgibbons May 18 '16 at 20:51
  • Strange, that should work. Maybe there is an odd blank in the text list. Also, add ,FALSE as a fourth parameter in the VLOOKUP(); that tells Excel to check for exact matches (the default is to take the nearest match): =VLOOKUP(<cell with the dropdown>,TheTable,2,FALSE). If you have a misspelling or extra blank, that will flush it out, like Baot 1 or Boat 1. – Aganju May 18 '16 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.