validation - how to show the second column

I've highlited a column and put a validation having the range in a different
spreadsheet. In the spreadsheet with the range I have to columns - Code and
Description. But, for the validation I'm using only the first Code column for
data entries.
The validation is working well but a user entering Code can see only Code.
It would be nice to show the user the Description as well - not for entries
but just to see the Code's description.

How could I show the user the second column?

Hi,
You could use VLOOKUP to display description in adjacent column to
Code entry.

Code in Column A, Description to be placed in B, then in col B - starting
row 2, put:

=F(iserror(vlookup(A2,MyTable,2,false)), " ",vlookup(A2,MyTable,2,false))

where "MyTable" is named range containing Code and Description.

Copy down in column B.

HTH

"Alex" wrote:

> I've highlited a column and put a validation having the range in a different
> spreadsheet. In the spreadsheet with the range I have to columns - Code and
> Description. But, for the validation I'm using only the first Code column for
> data entries.
> The validation is working well but a user entering Code can see only Code.
> It would be nice to show the user the Description as well - not for entries
> but just to see the Code's description.
>
> How could I show the user the second column?

Thanks a lot Toppers.
But, I wouldn't like to enter the data from the Description column. I'd like
just to show it to the user during the Code entries as in MS Access for the
second (or more) not bound column in a combobox.

"Toppers" wrote:

> Hi,
> You could use VLOOKUP to display description in adjacent column to
> Code entry.
>
> Code in Column A, Description to be placed in B, then in col B - starting
> row 2, put:
>
> =F(iserror(vlookup(A2,MyTable,2,false)), " ",vlookup(A2,MyTable,2,false))
>
> where "MyTable" is named range containing Code and Description.
>
> Copy down in column B.
>
> HTH
>
> "Alex" wrote:
>
> > I've highlited a column and put a validation having the range in a different
> > spreadsheet. In the spreadsheet with the range I have to columns - Code and
> > Description. But, for the validation I'm using only the first Code column for
> > data entries.
> > The validation is working well but a user entering Code can see only Code.
> > It would be nice to show the user the Description as well - not for entries
> > but just to see the Code's description.
> >
> > How could I show the user the second column?

Using the worksheet change event you could try several options.

restrict the change event by using

Private Sub Worksheet_Change(ByVal Target As Range)
Dim str as String
If Target.Address = $A$1 ' set to cell adress of Data
Validation
str = worksheetfunction.Vlookup(target,LookupTable,2,2false)
With Target.Validation
.InputMessage = str
End With

End Sub

or you can use a variation on this to create or edit the cells comment,
making sure that
the comment is visible at all times (or only visible when the cell is the
active cell)

--
steveB

Remove "AYN" from email to respond
"Alex" <[email protected]> wrote in message
news:[email protected]...
> Thanks a lot Toppers.
> But, I wouldn't like to enter the data from the Description column. I'd
> like
> just to show it to the user during the Code entries as in MS Access for
> the
> second (or more) not bound column in a combobox.
>
> "Toppers" wrote:
>
>> Hi,
>> You could use VLOOKUP to display description in adjacent column to
>> Code entry.
>>
>> Code in Column A, Description to be placed in B, then in col B -
>> starting
>> row 2, put:
>>
>> =F(iserror(vlookup(A2,MyTable,2,false)), " ",vlookup(A2,MyTable,2,false))
>>
>> where "MyTable" is named range containing Code and Description.
>>
>> Copy down in column B.
>>
>> HTH
>>
>> "Alex" wrote:
>>
>> > I've highlited a column and put a validation having the range in a
>> > different
>> > spreadsheet. In the spreadsheet with the range I have to columns - Code
>> > and
>> > Description. But, for the validation I'm using only the first Code
>> > column for
>> > data entries.
>> > The validation is working well but a user entering Code can see only
>> > Code.
>> > It would be nice to show the user the Description as well - not for
>> > entries
>> > but just to see the Code's description.
>> >
>> > How could I show the user the second column?