Drop-down list to adjust data in Excel worksheet?

Question

I'm creating a run down of possible expenses in Excel for a wedding, and I'd like to create a drop-down list that adjusts costs. I'm no Excel expert and I'm trying to find proper tutorials regarding this, can anyone help? :)

Example:
I'd like to choose from a list of Wedding Photographer's that each have different rates when I'm in my Excel sheet. Choosing from the drop down list will adjust the cost accordingly in a comprehensive expense sheet.

When I choose "John Doe", for example, then his total cost (in the adjacent cell) will change. If I choose someone else from the drop-down list, then the number changes accordingly.

A1: Photographer Name (the drop down list)
B1: Actual Cost (number that appears based on selection in A1)

1. How can I properly create this sort of drop-down list?
2. Is it easy to add additional photographers and their corresponding cost later on?

Thanks so much in advance!

Answer 1

I'm not sure I would recommend this for your spreadsheet.

Why not simply list the photographer's names and the prices next to them on your spreadsheet in a Category for Photography Cost. Then you can see all of them at once and choose.

Otherwise, you can create a drop down list (go to Excel Help and it will tell you how). You will need to type in the names and prices of the phtographers somewhere in your spreasdsheet or on another sheet.

Then you can use vlookup in the cell next to the dropdown list.
You can use the same list from the drop down list for this information. This list must be in alphabetical order.

Answer 2

A Vlookup will do the job. The Vlookup formula below will work when entered into column H of your sheet. For column J change the 2 to a 3 and column L change it to 4. The formula assumes that the range of cells where the values are is A1:D3 on the BSP sheet. The drop down list values are in column A. =VLOOKUP($J$2,BSP!$A$1:$D$3,2)

Answer 3

I concur with vbmica. Stick with the lookup functions and use that. This will keep you away from macros/vba work. Makes the sheet more dynamic for charting as well.

God Bless

Frank Pytel