Dave

very neat - Iwasn't entirely sure what was going on (and that was when my
mind was still fresh in the morning <g)

Tim

"Dave Peterson" wrote in message
...
I don't think I'd use VBA for this.

You've got a list of company names (in a different (hidden) worksheet)
that you
use for Data|validation.

Next to those company names, put the attention name.

then you can use a vlookup() formula to get that attention name.

=if(c5="","",vlookup(c5,sheet3!$a$1:$b$9999,2,fals e))

I find it easier to update a table in a worksheet when things change than
in the
code.



billabong wrote:

Hello

Im a beginner to VBA in excel and this is probably the wrong way to go
about it...

But I want to have a list of companies in a column. When the user
selects a company that will create a column with the "attention name"
with respect to the company. Then have both company and attetnion
appear in another column. I have done this with excels data
validation for the companies name but am having problems with the
attention part. Ive been trying with the apollo part only using
select case

Any suggestions is appreciated
Lisa

Select Case Cells(5, "c")
Case "Apollo"
Cells(2, "A") = "APOLLO "

'Cells(9, "h") = "Attention smith"
'Cells(10, "h") = "Attention john"

With Selection.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=$H$9:$H$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Cells(3, "a") = Cells(5, "e")

Case "brooks"
Cells(2, "a") = "Brooks ltd"

Case "peterson"
Cells(2, "a") = "peterson ltd"


Case Else
Cells(2, "B") = ""


End Select

--

Dave Peterson