Table of valid codes

  • Thread starter Thread starter Quimera
  • Start date Start date
Q

Quimera

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
 
Sorry, it is just the code I need returned. The description is a
reference only to help select the correct code. Thanks.
 
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
 
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.
 
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.
 
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?
 
Back
Top