Linking Cells In a Dropdown Box

[Kerry78]

Hi,

I am not very good at explaining, but I have a painting company and I am creating a spreadsheet to price jobs.

I want to be able to click on a dropdown box, select a painting description(e.g: To Paint Ceilings) and have excel automatically pull up a cell beside it with the price for painting ceilings.

e.g To Paint Ceilings - $1.00
To Plaster Walls - $2.00

The reason I want the spreadsheet set out this way is so I can then calculate the price in the second cell based on the room measurements in the third cell, which I am able to do myself.

e.g.
Description - Price - Area (M2) - Total Cost
To Paint Ceilings - $1.00 - 5 - $5.00
To Plaster walls - $2.00 - 20 - $40.00

I hope this makes sense and that somebody can help me.
Thanks in advance
Kerry

[littleiitin]

Hi Kerrry,

Welcome to the board,

Make a list of your description and Give price in next column. Make a name "rngList" of all your Discription.

I have applied date validation in column 1 and row 75...

Please change accordingly..
Paste this VBA code in sheets code window........




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row <= 75 Then
Target.Offset(, 1) = Range("rngList").Find(Target.Value).Offset(, 1).Value
End If
End Sub



HTH
--------------------------

[Admin]

Hi Kerry,

Welcome to ExcelFox !!

You could try VLOOKUP function to accomplish this task.

If your dropdown value in A2,

In B2

=VLOOKUP(A2,Sheet2!$A$2:$B$10,2,0)

where

Sheet2!A2:B10 holds the Description (Col A) and Price (Col B)

Post back if any query.