Help with Data valadation List

[StubbornOx]

I am not sure how to ask this question but here goes. I am trying to create a form where when I select an item for a drop down box (data validation List) It automatically selects the appropriate data and places it into the next column. For example, if I select payroll in the list it automatically puts 073-119-043, but If i select travel it puts 073-306-871 in the next column.

[NBVC]

You would need to create a table of values somewhere on the side which stores the selectable items and the corresponding codes. Then use VLOOKUP

e.g.

=Vlookup(A1,'Sheet2'!A:B,2,FALSE)

This looks up item in your dropdown list in A1 and finds it in Sheet2, column A, and returns corresponding item from column B.

[MarvinP]

Hi StubbornOx and welcome to the forum.

There are always a few methods that work. NBVC suggested one but I was thinking a Multi Column ComboBox may also work, depending on what you finally need.

Read http://www.contextures.com/Excel-VBA...Box-Lists.html

[ChemistB]

You would want to put a vlookup in the next column and set up a table, let's say on sheet2 A2:C50. So if your data validation is in A1 of sheet1, in A2 you'd have something like

=IF(A1<>"", VLOOKUP(A1, Sheet2!$A$2:$C$50,2,FALSE),"")
If you name the table on sheet2, i.e. "Codes" this formula becomes
=IF(A1<>"", VLOOKUP(A1, Codes, 2, False), "")
Any questions?

[MarvinP]

Why didn't you use code tags?
Is that a question?

[ChemistB]

Why didn't you use code tags?

Answer found here.

http://www.excelforum.com/the-water-...code-tags.html
I'm still on the fence. Perhaps I shall on odd #'d posts.

[MarvinP]

I got scolded a few days back for not putting code tags on a very short and inline phrase.

I guess it's Moderator Preference and how they are feeling today.

Thanks for showing your independence. I think we all have brains but some of us have special preferences.

HEY - you could do a little random number generator that would replace Code tags with Quote Tags depending on....

[StubbornOx]

Thanks NVBC that worked.

[ChemistB]

Thanks NVBC that worked.

Sureeeeee, just because he's faster than us and better at excel and gets his post in first.
Chemistb <-- chopped liver

[MarvinP]

That new symbol of
"<--" Chopped Liver
Is that read "Less than chopped liver" or "Less than negativie chopped liver"?