Validation based on selected criteria

[mrMozambique]

Hello all. I'll paste my spreadsheets first and then proceed with my question below. please note that these are actually two different sheets (one raw data set up sheet<top> and the other a data entry sheet for end users<bottom>). I put them together to avoid the java errors.

mrexcel.xls
	A	B	C	D	E
1	Programa	TipodeDespesa	ActivityTitle	Value	Program_Object
2	ADMIN	ACTIVITIES	English course for two - $1260/180 hour course X 2 courses	$5,040	ADMINACTIVITIES
3	ADMIN	ACTIVITIES	Basic computer course for 10 persons - $350/person X 10 persons	$3,500	ADMINACTIVITIES
4	ADMIN	ACTIVITIES	OtherActivities	$-	ADMINACTIVITIES
5	ADMIN	CONSULTANT	Redesign financial and accounting tracking system	$4,975	ADMINCONSULTANT
6	ADMIN	CONSULTANT	Continuation of TA Contract for NGO Contract Management (65 days X 2 consultants X $350/day)	$47,775	ADMINCONSULTANT
7	ADMIN	CONSULTANT	OtherConsulting		ADMINCONSULTANT
8	ADMIN	EQUIPMENT	DesktopComputer(TypePentiumIV)	$2,000	ADMINEQUIPMENT
9	ADMIN	EQUIPMENT	Laptops(TypePentiumIV)	$2,500	ADMINEQUIPMENT
10	ADMIN	EQUIPMENT	Printers(TypeLaserjet2100)	$500	ADMINEQUIPMENT
11	ADMIN	EQUIPMENT	LCDProjector	$3,400	ADMINEQUIPMENT
12	ADMIN	EQUIPMENT	FlashDrive	$200	ADMINEQUIPMENT
13	ADMIN	EQUIPMENT	PhotocopyMachine	$11,500	ADMINEQUIPMENT
14	ADMIN	EQUIPMENT	Smallrefrigerator	$800	ADMINEQUIPMENT
15	ADMIN	EQUIPMENT	OtherEquipment	$-	ADMINEQUIPMENT
16	ADMIN	OTHER	Accounting Department fees/DNS ($1500/month)	$18,000	ADMINOTHER
17	ADMIN	OTHER	Internetserviceprovider	$15,000	ADMINOTHER
18	ADMIN	OTHER	Dedicated leasedlinefor internet service	;11,000	ADMINOTHER
19	ADMIN	OTHER	Inter-departmental accounting costs ($300/monthX12months)	3,600	ADMINOTHER
20	ADMIN	OTHER	Other		ADMINOTHER
21
22	Programa	TipodeDespesa	Concatenate	Detalhe-Despesa	Amount
23	ART	CONSULTANT	ARTCONSULTANT	$150/day consultancy to assess quality of care provided at Day Hospitals	575
24	BIOSAFETY	EQUIPMENT	BIOSAFETYEQUIPMENT	Assessment of sterilization procedures and inventory of sterilization equipment - Per diems: 4 persons x $50/day x 7days	257
25	CMAM	OTHER	CMAMOTHER	TechnicalClothes	3,005
26	ADMIN	OTHER	ADMINOTHER	Internetserviceprovider	1,500
27	ADMIN	ACTIVITIES	ADMINACTIVITIES	English course for two - $1260/180 hour course X 2 courses	1,500
28	ADMIN	EQUIPMENT	ADMINEQUIPMENT	Printers(TypeLaserjet2100)	1,500
Sheet1

The top table is a budgeting tool that we use at the Mozambican Ministry of Health. We have programs (column A), expense types (column B) and expense details (column C) that are all set up at the beginning of the budgeting period. Column E is a concatenation of columns A&B that I have been hoping to use to define ranges (more on that in a minute). So, after we have established the budget and various expenses, we start spending money.

Our finance guys use the 2nd table to track these expenses. They select the program area (Column A) and expense type (column B) from dropdowns. I then have concatenated their selection in column C (hidden from the user). Based on their selections in the first two columns, I want ONLY those activities entered in column C of the top table to appear in Column D of the bottom table. In other words, a validation list dropdown with the named range from the concatenation in column C of bottom table. The validation dropdown will only include expenses that correspond to the program and expense type already selected in columns A&B. I've been able to use the Indirect function (ex. cell D23's validation = indirect(C23).

This only works now because I have manually named the ranges in the top table's column C as the corresponding name in column E. So, C2-C4 are a named range "ADMINACTIVITIES" and C5-C7 are named "ADMINCONSULTANT", etc. etc. So, when the user selects "ADMIN" in A27 and "ACTIVITIES" in B27, D27 turns into a dropdown list of manually named range "ADMINACTIVITIES" based on Indirect(C27). Again, this only works now because I've manually created the range "ADMINACTIVITIES" in the above table.

Shouldn't it be possible to use the OFFSET and COUNTIF functions together somehow to create the validation automatically without the need for manually named ranges? I'm open to other suggestions, but I need to be able to turn this tool over to my Ministry colleagues without the need to manually name ranges.

Any suggestions? I can't be the first to face this problem so I thought I'd throw it out to the puzzle-solvers here. Thanks a ton in advance!

 

[Andrew Poulsom]

In your data set up sheet sheet name column C Activities and column E Programs. In your data entry sheet in D23 you can use Data Validation, List with Source:

=INDEX(Activities,MATCH(C23,Programs,FALSE)):INDEX(Activities,MATCH(C23,Programs,FALSE)+COUNTIF(Programs,C23)-1)

 

[mrMozambique]

Andrew, thanks so much. I should have come here a long time ago! It works beautifully. the Ministry of Health in Mozambique sends its regards!

Cheers!

 

[mrMozambique]

In your data set up sheet sheet name column C Activities and column E Programs. In your data entry sheet in D23 you can use Data Validation, List with Source:

=INDEX(Activities,MATCH(C23,Programs,FALSE)):INDEX(Activities,MATCH(C23,Programs,FALSE)+COUNTIF(Programs,C23)-1)

I have been using this spreadsheet for a while now and then transferred the use of the abovementioned formula to another sheet that I'm using. I have several validated cells next to it. When I'm tabbing or arrowing through these cells, they move extremely slowly. Is it possible that this validation is causing some performance issues? It's really obnoxious to wait while the next cell is selected. I'm not even changing the cells, only selecting them.

Thanks from Mozambique.