Table of valid codes
 

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.

Table of valid codes
 

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.

Table of valid codes
 

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.

Table of valid codes
 

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.

Table of valid codes
 

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.

Table of valid codes
 

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

Table of valid codes
 

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

Table of valid codes
 

Yikes! I am just a basic Excel user, and I think I must have been dead
wrong in assuming there was a simple solution. This is what I do now,
which is simple enough but a nuisance.

Worksheet 1 requires a classification code for each detail financial
transactio. The general description of the code is not required on
Worksheet 1, only the detail of the specific transaction. For example,
Code 742 represents "Office Expenses" in general, but the detail
transaction description would be typed as e.g. "Printer cartridges".

Worksheet 2 is a summary list of the transactions for each code. i.e.
one row for each code. Column 1 is the code, Column 2 is the general
description e.g. "Office Expenses" and Column 3 is the total (updated
automatically from Worksheet 1) for all detail transactions for Code
(nnn)

The simple solution is to open Worksheet 2 to identify the correct
classification code (if the user is not sure which one to use) and
copy/paste it into Worksheet 1.

What I had hoped to achieve was some way to display a
selection/validation list of the codes and general description while in
Worksheet 1 without having to look it up in Worksheet 2.

It it can't be done, it will just have to remain one of life's little
annoyances.

Dave Peterson" wrote in message
...
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

Table of valid codes
 

You had a suggestion to use another cell and use =vlookup().

You had a suggestion to use data|Validation and a worksheet event.

You had a suggestion to use a combobox from the control toolbox toolbar.

Why do you think that it can't be done?

Quimera wrote:

Yikes! I am just a basic Excel user, and I think I must have been dead
wrong in assuming there was a simple solution. This is what I do now,
which is simple enough but a nuisance.

Worksheet 1 requires a classification code for each detail financial
transactio. The general description of the code is not required on
Worksheet 1, only the detail of the specific transaction. For example,
Code 742 represents "Office Expenses" in general, but the detail
transaction description would be typed as e.g. "Printer cartridges".

Worksheet 2 is a summary list of the transactions for each code. i.e.
one row for each code. Column 1 is the code, Column 2 is the general
description e.g. "Office Expenses" and Column 3 is the total (updated
automatically from Worksheet 1) for all detail transactions for Code
(nnn)

The simple solution is to open Worksheet 2 to identify the correct
classification code (if the user is not sure which one to use) and
copy/paste it into Worksheet 1.

What I had hoped to achieve was some way to display a
selection/validation list of the codes and general description while in
Worksheet 1 without having to look it up in Worksheet 2.

It it can't be done, it will just have to remain one of life's little
annoyances.

Dave Peterson" wrote in message
...
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

--

Dave Peterson