Substition for data validation, dropdown list

[Russ G]

Hi, i am new to this group and am a relative beginner without formal
training. I've created an invoice for work and I have one column with a
dropdown list that I created using the great info from:

http://www.contextures.com/xlDataVal01.html

What I am trying to do is enter a billing code which is a number several
digits long. I was able to create this using the data validation function.
However, it requires the user to remember which billing code to use. Each
code has a descriptive name (very easy to remember) but it is not necessary
to put that name in the invoice, only the code number. I would like the
dropdown list to contain the descriptive name, but when you select the name,
instead of the name being entered in the cell, it enters the code number.

For example, the code for "repair" might be "3A245134", it is easier to
remember "repair" than the number. So, from the dropdown list, one of the
selections would be "repair". You select repair and the number "3A245134"
appears in the cell.

Is there a simple way to do this?

 

[Pete_UK]

If you put your drop-down to the right of the Invoice so it does not
get printed, then you can have it select from the descriptions, and
then in the cell where you want the code to appear you can either have
a VLOOKUP formula or an INDEX/MATCH combination, depending on how your
data is laid out.

Post back with details of your data table if you need further
guidance.

Hope this helps.

Pete

 

[Russ G]

Pete, Thanks for the info but I need to keep this as simple as possible. If
I need to add another column, it will confuse more people than help and I am
also restricted to a certain format (gov't regulations!). The speadsheet is
otherwise simple and basically a duplicate of a preprinted form. I'm trying
to move people over to the electronic version and want to make it as user
friendly as possible. There is only one column that is suitable for a
dropdown list. If there is no other way to accomplish what I want, I'll just
use a list of codes.
--
Thanks for your help
Russ

If you put your drop-down to the right of the Invoice so it does not
get printed, then you can have it select from the descriptions, and
then in the cell where you want the code to appear you can either have
a VLOOKUP formula or an INDEX/MATCH combination, depending on how your
data is laid out.

Post back with details of your data table if you need further
guidance.

Hope this helps.

Pete

 

[T. Valko]

There is a sample file at Contextures that does exactly what you want.

http://contextures.com/excelfiles.html#DataVal

Look for:

DV0004 - Data Validation Change -- Select a Product from the Data Validation
list; an event procedure changes the product name to a product code.

--
Biff
Microsoft Excel MVP

Pete, Thanks for the info but I need to keep this as simple as possible.
If
I need to add another column, it will confuse more people than help and I
am
also restricted to a certain format (gov't regulations!). The speadsheet
is
otherwise simple and basically a duplicate of a preprinted form. I'm
trying
to move people over to the electronic version and want to make it as user
friendly as possible. There is only one column that is suitable for a
dropdown list. If there is no other way to accomplish what I want, I'll
just
use a list of codes.

 

[Russ G]

Thanks, it is exactly what I want to do. Looks a bit complex at my level but
I am going to play with it and see what I can do.

 

[T. Valko]

Looks a bit complex

Well, you did say:

What Pete suggested is the simplest way to go!

 

[Russ G]

Biff,

I tried coping the code into the code window for my spreadsheet and changed
the names of the sheet and list to mine. I also put my lists in the same
location on the worksheet, but it doesn't work. I'm going to see if I can
find someone facile w/ visual basic who can help me trouble shoot, umless you
can think of something I didn't do?

 

[T. Valko]

Hard to say why it isn't working. I downloaded the sample file and checked
it out. It's pretty straightfoward.

When you say it doesn't work do you get any errors? The code is for an event
macro. This code has to be placed in the correct place to work. Are you sure
it's in the correct place?

I'll take a look at your file if you want to send a copy to me. Just let me
know how to contact you.

 

[Russ G]

Biff,

Thanks for sticking with me and providing support. I finally figured it
out. I took the original sample spreadsheet and I moved the data around,
changed values, etc. so I could see what needed to be adjusted in the code to
allow it to continue to work. I discovered that the issue was the column
specified as column 2, refered to the sheet with the data validation, not the
sheet with the raw data. My speadsheet had the data validation in column 3,
so I changed it and it works!

This was an interesting experience and has made me interested in learning
how to program visual basic. Thanks again.

 

[T. Valko]

Glad you got it working. Thanks for feeding back!