Help please!! Data validation & automatically filling ce

[tcy999]

Hi there..
I have a data validation list containing the following items in column A(along with a blank cell at the top to allow a blank option)..

I need to automatically fill the next column (B) depending on which of these options is chosen, for example, if one of the first 4 options is chosen, then column B next to it needs to automatically say "Administration"....

Is this possible? I've tried several different methods but no joy - I'm sure there must be a simple solution!

Cost
Booking Error
Appointment delay
Unhelpful staff
Partner contacted by mistake
Late practitioner
Practitioner absent
Dissatisfied with outcome
Remarks by practitioner
Practitioner skills
Unprofessional conduct
Service not delivered
Poor facilities
Noisy facilities
Lack of privacy
Family facilities
Confidentiality: Outside Centre
Confidentiality: Socially
Confidentiality: Informing other agency
Discrimination on grounds of sex
Discrimination on grounds of ethnicity
Discrimination on grounds of sexuality
Discrimination on grounds of disability

 

[erik.van.geit]

Hi, tcy999,

can this help you ?

Map1
	A	B	C	D	E	F
1	Unhelpful staff	b			Cost	a
2					Booking Error	a
3					Appointment delay	a
4					Unhelpful staff	b
5					Partner contacted by mistake	b
6					Late practitioner	c
7					Practitioner absent	c
Blad1 (2)

kind regards,
Erik

 

[tcy999]

Sorry bit of a newbie and don't understand....
The following formula seems to work but not with this many arguments - could you tell me whats wrong with it (apart from looking like a nightmare?!)

=IF(A8=L19, “Administration”,IF(A8=L20, “Administration”,IF(A8=L21, “Administration”,IF(A8=L22, “Administration”,IF(A8=L23, “Administration”,IF(A8=L24, “Service Delivery”,IF(A8=L25, “Service Delivery”))))))), (ANDIF(A8=L26, “Service Delivery”,IF(A8=L27, “Service Delivery”,IF(A8=L28, “Service Delivery”,IF(A8=L29, “Service Delivery”,IF(A8=L30, “Service Delivery”,IF(A8=L31, “Environment”,IF(A8=L32, “Environment”))))))), (ANDIF(A8=L34, “Environment”,IF(A8=L35, “Confidentiality”,IF(A8=L36, “Confidentiality”,IF(A8=L37, “Confidentiality”,IF(A8=L38, “Discrimination”,IF(A8=L39, “Discrimination”,IF(A8=L40, “Discrimination”))))))),(ANDIF(A8=L41, “Discrimination”,IF(A8=L33, “Environment”)))

 

[erik.van.geit]

making formulas can be done "in steps"
divide your formula into some parts using different cells, afterwards you can put them together ...

example:

build formula in steps.xls
	A	B	C	D
1	250	1		putting together
2	100	0		350
3		1		350
4	in steps	350		350
1

kind regards,
Erik

 

[erik.van.geit]

formulas are not displayed

B1 =IF(A1>100,1,0)		
B2 =IF(A2<100,1,0)
B3 =SUM(B1;B2)		
B4 =B3*(A1+A2)	

D2 =SUM(B1;B2)*(A1+A2)
D3 =SUM(IF(A1>100,1,0),B2)*(A1+A2)
D4 =SUM(IF(A1>100,1,0),IF(A2<100,1,0))*(A1+A2)