This is a common function in application systems, and I am sure there is
a simple way to do it in Excel, but so far I haven't been smart enough
to figure it out.

I have a column of account codes on Worksheet 1. I want to be able to
select a code cell, open Worksheet 2 containing one column for all valid
codes and one column for the descriptions, select a code from Worksheet
2 and have it automatically inserted into the code cell of Worksheet 1.

Thanks for your help.

Is this what you mean?

Sheet1 A1 = code XX

Find code XX in column A of Sheet2 and return its description from column B
of Sheet2.

=VLOOKUP(A1,Sheet2!A:B,2,0)

Biff

"Quimera" wrote in message
news:d8I3i.40720$Xh3.35416@edtnps90...
This is a common function in application systems, and I am sure there is a
simple way to do it in Excel, but so far I haven't been smart enough to
figure it out.

I have a column of account codes on Worksheet 1. I want to be able to
select a code cell, open Worksheet 2 containing one column for all valid
codes and one column for the descriptions, select a code from Worksheet 2
and have it automatically inserted into the code cell of Worksheet 1.

Thanks for your help.

Sorry, it is just the code I need returned. The description is a
reference only to help select the correct code. Thanks.



"T. Valko" wrote in message
...
Is this what you mean?

Sheet1 A1 = code XX

Find code XX in column A of Sheet2 and return its description from
column B of Sheet2.

=VLOOKUP(A1,Sheet2!A:B,2,0)

Biff

"Quimera" wrote in message
news:d8I3i.40720$Xh3.35416@edtnps90...
This is a common function in application systems, and I am sure there
is a simple way to do it in Excel, but so far I haven't been smart
enough to figure it out.

I have a column of account codes on Worksheet 1. I want to be able
to select a code cell, open Worksheet 2 containing one column for all
valid codes and one column for the descriptions, select a code from
Worksheet 2 and have it automatically inserted into the code cell of
Worksheet 1.

Thanks for your help.

Do you mean something like a drop down list where you can select the code?

Biff

"Quimera" wrote in message
news:nhM3i.40775$Xh3.14567@edtnps90...
Sorry, it is just the code I need returned. The description is a
reference only to help select the correct code. Thanks.



"T. Valko" wrote in message
...
Is this what you mean?

Sheet1 A1 = code XX

Find code XX in column A of Sheet2 and return its description from column
B of Sheet2.

=VLOOKUP(A1,Sheet2!A:B,2,0)

Biff

"Quimera" wrote in message
news:d8I3i.40720$Xh3.35416@edtnps90...
This is a common function in application systems, and I am sure there is
a simple way to do it in Excel, but so far I haven't been smart enough
to figure it out.

I have a column of account codes on Worksheet 1. I want to be able to
select a code cell, open Worksheet 2 containing one column for all valid
codes and one column for the descriptions, select a code from Worksheet
2 and have it automatically inserted into the code cell of Worksheet 1.

Thanks for your help.

A drop down list containing only the codes is not helpful in this
situation. A description of each code is required in order to make the
correct selection...

100 Cash Bank
105 Trust Bank
110 Guaranteed Investment Certificates
120 Accounts Receivable Cash
130 Accounts Receivable Trust
190 Membership fee deposits
191 Membership fee remittances


"T. Valko" wrote in message
...
Do you mean something like a drop down list where you can select the
code?

Biff

"Quimera" wrote in message
news:nhM3i.40775$Xh3.14567@edtnps90...
Sorry, it is just the code I need returned. The description is a
reference only to help select the correct code. Thanks.



"T. Valko" wrote in message
...
Is this what you mean?

Sheet1 A1 = code XX

Find code XX in column A of Sheet2 and return its description from
column B of Sheet2.

=VLOOKUP(A1,Sheet2!A:B,2,0)

Biff

"Quimera" wrote in message
news:d8I3i.40720$Xh3.35416@edtnps90...
This is a common function in application systems, and I am sure
there is a simple way to do it in Excel, but so far I haven't been
smart enough to figure it out.

I have a column of account codes on Worksheet 1. I want to be
able to select a code cell, open Worksheet 2 containing one column
for all valid codes and one column for the descriptions, select a
code from Worksheet 2 and have it automatically inserted into the
code cell of Worksheet 1.

Thanks for your help.

I, too, would use an additional cell. I think it makes it easier for both the
developer and the user. The user knows what code they picked and the developer
uses an =vlookup().

But if you don't want to and you're running xl2k+ (won't work in xl97), you
could use a worksheet change event that replaces the code type with the
description.

If you want to try this, right click on the worksheet tab that has that cell
with data|validation. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Variant
Dim DVList As Range

On Error GoTo ErrHandler:

Set DVList = Me.Parent.Worksheets("sheet2").Range("myList")

With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

res = Application.Match(.Value, DVList, 0)

If IsError(res) Then
'do nothing
Else
Application.EnableEvents = False
.Value = DVList(res).Offset(0, 1).Value
End If
End With

ErrHandler:
Application.EnableEvents = True

End Sub

Notice that A1 held the data|validation in my sample and my list used for
data|validation was named myList.

Change these if you need to. Then back to excel to test it.

Quimera wrote:

A drop down list containing only the codes is not helpful in this
situation. A description of each code is required in order to make the
correct selection...

100 Cash Bank
105 Trust Bank
110 Guaranteed Investment Certificates
120 Accounts Receivable Cash
130 Accounts Receivable Trust
190 Membership fee deposits
191 Membership fee remittances

"T. Valko" wrote in message
...
Do you mean something like a drop down list where you can select the
code?

Biff

"Quimera" wrote in message
news:nhM3i.40775$Xh3.14567@edtnps90...
Sorry, it is just the code I need returned. The description is a
reference only to help select the correct code. Thanks.



"T. Valko" wrote in message
...
Is this what you mean?

Sheet1 A1 = code XX

Find code XX in column A of Sheet2 and return its description from
column B of Sheet2.

=VLOOKUP(A1,Sheet2!A:B,2,0)

Biff

"Quimera" wrote in message
news:d8I3i.40720$Xh3.35416@edtnps90...
This is a common function in application systems, and I am sure
there is a simple way to do it in Excel, but so far I haven't been
smart enough to figure it out.

I have a column of account codes on Worksheet 1. I want to be
able to select a code cell, open Worksheet 2 containing one column
for all valid codes and one column for the descriptions, select a
code from Worksheet 2 and have it automatically inserted into the
code cell of Worksheet 1.

Thanks for your help.









--

Dave Peterson

This sample from my archives might indicate one way:
http://savefile.com/files/221672
Combo box from control toolbox toolbar_Example2.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Quimera" wrote in message
news:EmV3i.45102$Xh3.33912@edtnps90...
A drop down list containing only the codes is not helpful in this
situation. A description of each code is required in order to make the
correct selection...

100 Cash Bank
105 Trust Bank
110 Guaranteed Investment Certificates
120 Accounts Receivable Cash
130 Accounts Receivable Trust
190 Membership fee deposits
191 Membership fee remittances