Vlookup or Visual Basic help

[Co1e]

Thanks for letting me join. I'm way over my head and neck deep. LOL

This is for a charity that serves people with disabilities I'm trying to help out.

(Capture 1 Image) I need multiple drop down list that replaces the text of the available selections with a numerical number. For example when the user selects a County from a drop down list the associated County Code is placed in that cell. Same for the Disability and it's code, Service and its code.

(Capture 2 & 3 Image, different tab/sheet) I was able to get it to work once, Capture 2 image cell I30, using some code copied from a Youtube video, Capture 3 image, but have issues trying to make it work again. Probably cause I know nothing on the Visual Basic basics.

The end goal would be to have the user be able to enter clients served by the charity and have these codes populate the spreadsheet that will be sent to the funding authority for quarterly reports. Thanks in advance for any help pointing me in the right direction.

 

[Joe4]

Welcome to the Board!

Why do you need VBA at all? Can't you just set it up using VLOOKUP formulas?
You can pre-populate the sheet with all the formulas you need, and hide them until there is a value in the lookup cell, i.e.
if we are looking up a value in cell I30, we could write a formula structured like:

=IF(I30="","",VLOOKUP(I30,...))

This essentially says if I30 is blank, return nothing. Otherwise, do the VLOOKUP on the value in cell I30 and return that.

 

[Co1e]

Welcome to the Board!

Why do you need VBA at all? Can't you just set it up using VLOOKUP formulas?
You can pre-populate the sheet with all the formulas you need, and hide them until there is a value in the lookup cell, i.e.
if we are looking up a value in cell I30, we could write a formula structured like:

=IF(I30="","",VLOOKUP(I30,...))

This essentially says if I30 is blank, return nothing. Otherwise, do the VLOOKUP on the value in cell I30 and return that.

Thank you. I'll work that idea and see if I can get it done and post back success or failure. Hopefully success. Much appreciated.

 

[Joe4]

You are welcome.

Be sure to post back if you run into any issues trying to get it set up!

 

[Co1e]

Hey @Joe4 lil' update. Worked on this on and off a bit over the last day and still no luck. I probably didn't describe the issue as well as I could have. I did find an almost an exact issue thread on here return alternate value based on dropdown selection - Data Validation

I'm pretty sure it's going to take the VB script to get it done. I need this type function on three different columns with 3 different tables. I reached out to one of my friends at Bible study who's good with VB for a little help. If and when he can solve that issue I'll probably still have to ask for some help here again as I pretty it up putting the data tables on a separate sheet tab and the columns with the dropdown list on another sheet.

Little exclamation point says be sure to "Mark as solution". May be a week or so before I hear back from my buddy. I just wanted to come back here and provide a lil' update. And while I'm here thank you again for the help. Post back again when there is progress.

 

[myall_blues]

As Joe says do you need the VBA? You could just put data validation on the relevant columns (Dis, County), then return the code using VLOOKUP into an adjacent column.

Like so:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Disability	Dis code		County	County code
2	ADD	1		Cherokee	10
3	Epilepsy	9		Jackson	36
4	Other	16		Lauderdale	39
5
6
7
8
9	Case #	Name	Desc	SC	Sex	Age	Dis	Dis Code	Date	County	County Code	Action
10							Epilepsy	9		Jackson	36
11
Sheet1

Cell Formulas
Range	Formula
H10	H10	=VLOOKUP(G10,Table1[#All],2)
K10	K10	=VLOOKUP(J10,Table2[#All],2)

Cells with Data Validation
Cell	Allow	Criteria
G10	List	=$A$2:$A$4
J10	List	=$D$2:$D$4

 

[Co1e]

As Joe says do you need the VBA? You could just put data validation on the relevant columns (Dis, County), then return the code using VLOOKUP into an adjacent column.

Like so:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Disability	Dis code		County	County code
2	ADD	1		Cherokee	10
3	Epilepsy	9		Jackson	36
4	Other	16		Lauderdale	39
5
6
7
8
9	Case #	Name	Desc	SC	Sex	Age	Dis	Dis Code	Date	County	County Code	Action
10							Epilepsy	9		Jackson	36
11
Sheet1

Cell Formulas
Range	Formula
H10	H10	=VLOOKUP(G10,Table1[#All],2)
K10	K10	=VLOOKUP(J10,Table2[#All],2)

Cells with Data Validation
Cell	Allow	Criteria
G10	List	=$A$2:$A$4
J10	List	=$D$2:$D$4

That's workable for sure. Totally cool! I'd just have to have the coordinators enter data on one sheet then transfer the columns needed to the sheet with Names, DOB that can be sent to the funding agency. The funding agency doesn't need the disability name just the code not do they want the private info.

Thanks. A work in progress and all the progress part is thanks to this forum.

 

[Co1e]

columns needed to the sheet with Names, DOB OMITTED that can be sent to the

Edit to add omitted