Data Validation with Complex sentences

[toady]

Help! I’m a humanitarian aid worker stationed in <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">Sudan</st1lace></st1:country-region>, I’m in charge of monitoring and evaluation for a very complex program and I inherited tools (excel databases) that are very unuseful. I work with seven partners and all of them report on three objectives. Eight sub-ojectives and 117 indicators. To make it easier for them to report I’m trying to make drop down menus dependent on what is entered into a previous cell this is easy if you only work with one or two words, but in order to be clear I need my entire sentence in there: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
So when someone puts in one of the three objectives: <o></o>
<o> </o>
1. Enhance the capacity of state and county/locality governments to plan, manage and provide services to citizens.<o></o>
<o> </o>
2. Improve relationships between local government and its constituents to ensure more democratic and transparent local governance.<o></o>
<o> </o>
3. Deliver concrete improvements in livelihoods, education, health and water and sanitation that reinforce support for peace and contribute to government capacity for transparent, participatory planning and delivery of services.<o></o>
<o> </o>
I only want the sub-objectives for that objective to come up, so let’s say they choose objective three I only want the following sub-objectives to come up:<o></o>
<o> </o>
<TABLE style="WIDTH: 160.8pt; BORDER-COLLAPSE: collapse" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=214><TBODY><TR style="HEIGHT: 0.25in" height=24><TD style="PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 160.8pt; PADDING-RIGHT: 5.4pt; HEIGHT: 0.25in; PADDING-TOP: 0in" height=24 vAlign=top width=214 noWrap>3.1 Improved health services<o></o>
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 160.8pt; PADDING-RIGHT: 5.4pt; HEIGHT: 25.5pt; PADDING-TOP: 0in" height=34 vAlign=top width=214>3.2 <st1lace w:st="on"><st1:City w:st="on">Improved</st1:City> <st1:State w:st="on">WASH</st1:State></st1lace> service and infrastructure<o></o>
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 160.8pt; PADDING-RIGHT: 5.4pt; HEIGHT: 25.5pt; PADDING-TOP: 0in" height=34 vAlign=top width=214>3.3 Improved access to sanitation services<o></o>
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 160.8pt; PADDING-RIGHT: 5.4pt; HEIGHT: 25.5pt; PADDING-TOP: 0in" height=34 vAlign=top width=214>3.4 Improved education services and infrastructure<o></o>
</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 160.8pt; PADDING-RIGHT: 5.4pt; HEIGHT: 25.5pt; PADDING-TOP: 0in" height=34 vAlign=top width=214>3.5 Improved livelihood opportunities<o></o>
</TD></TR></TBODY></TABLE>
<o> </o>
Then depending on which sub-objective they choose I only want the indicators for that sub-objective to come up. <o></o>
<o> </o>
So if they choose sub-objective 3.1 the following indicators come up: <o></o>
<o> </o>
# of staff trained (<st1lace w:st="on">COs</st1lace>, midwives and CHWs) each year on RH/FP topics<o></o>
# of target health facilities providing improved FP/RH services according to MoH’s comprehensive package of health services.<o></o>
# of MCH units in PHCC rehabilitated, equipped and furnished according to BPHS standards <o></o>
# of clean delivery kits distributed<o></o>
# of people trained in maternal/newborn health through USG-supported programs<o></o>
# of supported health facilities have a constant stock of properly managed life-saving anti-malaria commodities (except mosquito nets)<o></o>
# of individuals that use anti-malaria commodities<o></o>
# of insecticide-treated nets distributed with USG funds<o></o>
# of deliveries with a trained traditional birth attendant (TBA) and/or maternal and child health workers (MCHW) in USG-assisted programs<o></o>
# of children under 5 years of age who received vitamin A from USG-supported programs <o></o>
# children less than 12 months of age who received DPT3 from USG-supported programs<o></o>
Percentage of children less than 12 months of age who received DPT3 from USG-supported programs<o></o>
Percentage of assisted deliveries by trained health service providers in USG supported counties<o></o>
# of County Health Offices in place and resourced within 2 years<o></o>
# of trainings for 1 county office supported<o></o>
# CHD fully supported to implement and monitor FP/RH programs including EPI<o></o>
# of quarterly meetings held to promote and organise health stakeholder meetings between state and county<o></o>
# of reports on RH, EPI, Morbidity submitted to State authorities during the year<o></o>
# of clinical staff trained in basic clinical skills<o></o>
# community health committees supported and given refresher training/follow up each year<o></o>
# coordination meetings with county official and community committees each year<o></o>
<o> </o>
<o> </o>
Can you tell me how to do this; is it possible? <o></o>
<o> </o>
<o> </o>
Thank you in advance, <o></o>
<o> </o>
Toady

 

[Gerald Higgins]

This is indeed possible.

Consider this simplified example.

Let's say you want to control input to Cell A1 to either A, B or C.
Then, depending on what you input into A1, you want to control input to Cell A2 to either A1, A2, A3 if A was entered, B1, B2, B3 if B was entered, and C1, C2, C3 if C was entered.
I think this is analogous to your request.
In cell A1, set data validation, using list, and specify the possible values, either directly, or by reference to a range.
Somewhere on the worksheet, let's say in Cells B1:B3, enter a formula that calculated the acceptable values, based on what was input to cell A1.
This is the formula for B1.

=if(A$1="A","A1",if(A$1="B","B1","C1"))

This is the formula for B2.

=if(A$1="A","A2",if(A$1="B","B2","C2"))

You can work out what it needs to be for B3.

Then, in cell A2, set data validation, using list, and specify B1:B3 as the range containing the acceptable values.

See what happens as you input different values to A1.

 

[toady]

Hi Mr. Excel, the formula isn't working for me, I'm using excel 2003 and my lists are on a different sheet;

But I also think I'm missing a step, because what I want is if you enter Objective 1 into A2 then I want the choices to be B2:B5, Objective 2 into A2 then I want the choices to be B6:B9.

I'm sorry I'm still very confused.

Thanks,

Tiana

 

[Gerald Higgins]

1) I'm not Mr Excel
2) Use Range names if the data is on different sheets.
3) That's fine, but use another range to look up the range that applies.
For example

=if(A2="YES",B2,B6)

and so on. Use THIS range as the source for the data validation.