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?

validation - how to show 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?

validation - how to show 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?

validation - how to show 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,2fa lse)
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" wrote in message
...
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?