To many IF's for xl2003

[BradleyS]

I have been informed that xl2003 can only use up to 7 IF statements and if I have to use more than this then there is probably a better way to create the formula.

I of course have no idea how to improve on the formula otherwise I wouldn't need to post it. So here is the formula I can use in xl2010, but can't in xl2003:

Excel 2010
	D
2	FLU1
Sheet1

Cell Formulas
Range	Formula
D2	=IF(DATE(1938,9,1)>=$B2,"FLU2",IF(DATE(1948,9,1)>=$B2,"FLU1",IF(DATE(1948,9,1)<$B2,IF(L2="Carer","ZFCARE",IF(L2="Chronic Heart Disease","ZFCHD",IF(L2="Chronic Liver Disease","ZFCLD",IF(L2="Chronic Neurological Disease","ZFCNS",IF(L2="Chronic Renal Disease","ZFCRD",IF(L2="Chronic Respiratory Disease","ZFCRES",IF(L2="Diabetes","ZFDM",IF(L2="Long-stay residential","ZFHOME",IF(L2="Immunosuppression","ZFIMM",IF(L2="Pregnant","ZFPREG",IF(L2="Stroke / TIA","ZFSTIA",IF(AND(B2<=DATE(2010,9,1),B2>DATE(2009,9,1)),"ZLF3",IF(AND(B2<=DATE(2011,9,1),B2>DATE(2010,9,1)),"ZFL2",IF(L2="","Unidentified",)))))))))))))),"N/A")))

Here is the table of data I need to find the correct code for in cell D. My above formula doesn't include Pneumcoccal as I was busy trying to get the FLU to work, but Pneumo will have to use the text in IMMTYPE column J to workout the code

Excel 2003
	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	DOB	01/09/2013	Code						IMMSTYPE	STAGE	REASONTXT
2		20/02/1944	69y 6m 12d	FLU1						FLU
3		02/11/1910	102y 9m 30d	FLU2						FLU
4		21/09/1942	70y 11m 11d	PNEU						PNEUMOPOLY
5		12/11/1970	42y 9m 20d	ZFDM						FLU		Diabetes
6		03/08/1963	50y 0m 29d	ZFCRES						FLU		Chronic Respiratory Disease
7		03/11/1962	50y 9m 29d	ZFSTIA						FLU		Stroke / Tia
8		17/03/1963	50y 5m 15d	ZFDM						FLU		Diabetes
9		15/01/1967	46y 7m 17d	ZFCRD						FLU		Chronic Renal Disease
10		20/10/1969	43y 10m 12d	ZFCHD						FLU		Chronic Heart Disease
11		05/10/1963	49y 10m 27d	ZFCNS						FLU		Chronic Neurological Disease
12		13/04/1960	53y 4m 19d	ZFCRES						FLU		Chronic Respiratory Disease
13		09/12/1969	43y 8m 23d	ZFSTIA						FLU		Stroke / Tia
14		28/10/1969	43y 10m 4d	Unidentified						FLU
15		01/09/2011	2y 0m 0d	ZFL2						FLUSIN	1
16		01/09/2010	3y 0m 0d	ZLF3						FLU
17		02/01/2005	8y 7m 30d	ZFCRES						FLUSIN	1	Chronic Respiratory Disease
18		08/01/2005	8y 7m 24d	ZFCRD						FLUSIN	1	Chronic Renal Disease
19		01/08/2011	2y 1m 0d	PNCHMG						PNEUMOCONJ13
Sheet1

Here are the rules that make up the code in column D

Excel 2003
	A	B
1	Code	Description
2	FLU1	INFLUENZA VACC AGE 65 - 74
3	FLU2	INFLUENZA VACC AGED 75 AND OVER
4	ZFCARE	FLU VACC <65 AT RISK MAIN CARER
5	ZFCHD	FLU VACC <65 AT RISK CHRONIC HEART DISEASE
6	ZFCLD	FLU VACC <65 AT RISK CHRONIC LIVER DISEASE
7	ZFCNS	FLU VACC <65 AT RISK NEUROLOGICAL DISEASE
8	ZFCRD	FLU VACC <65 AT RISK CHRONIC RENAL DISEASE
9	ZFCRES	FLU VACC <65 AT RISK RESPIRATORY DISEASE
10	ZFDM	FLU VACC <65 AT RISK DIABETES MELLITUS
11	ZFHOME	FLU VACC <65 AT RISK CARE HOME RESIDENT
12	ZFIMM	FLU VACC <65 AT RISK IMMUNOSUPPRESSED
13	ZFPREG	FLU VACC <65 AT RISK PREGNANT WOMEN
14	ZFSTIA	FLU VACC <65 AT RISK STROKE TIA
15	PNEU	PNEUMOCOCCAL INJECTION AGE 65 AND OVER
16	PNCHMG	CHILD PNEUMO VACC FINAL DOSE UNDER 5
17	ZFL2	INFLUENZA 2 YEAR OLDS
18	ZFL3	INFLUENZA 3 YEAR OLDS
Sheet2

Any help in this would be most appreciated as I have hundreds to check through

 

[markmzz]

You can use this code:

Sub Test()
    myStr = "=IF(DATEDIF(RC[-2],R1C3,""y"")>=75,""FLU2"",IF(DATEDIF(RC[-2],R1C3,""y"")>=65,""FLU1"",IF(RC[8]="""","
    myStr = myStr & "IF(OR(DATEDIF(RC[-2],R1C3,""y"")={2;3}),""ZFL""&DATEDIF(RC[-2],R1C3,""y""),IF(RC[6]=""PNEUMOCOCONJ13"","
    myStr = myStr & """PNCHMG"",IF(RC[6]=""PNEUMOPOLY"",""PNEU"",""UNIDENTIFIED""))),VLOOKUP(RC12,{""Carer"",""ZFCARE"";"
    myStr = myStr & """Chronic Heart Disease"",""ZFCHD"";""Chronic Liver Disease"",""ZFCLD"";""Chronic Neurological Disease"","
    myStr = myStr & """ZFCNS"";""Chronic Renal Disease"",""ZFCRD"";""Chronic Respiratory Disease"",""ZFCRES"";""Diabetes"",""ZFDM"";"
    myStr = myStr & """Long-stay residential"",""ZFHOME"";""Immunosuppression"",""ZFIMM"";""Pregnant"",""ZFPREG"";""Stroke / TIA"",""ZFSTIA""},2,0))))"
    Range("D2").FormulaR1C1 = myStr
End Sub

Markmzz

 

[BradleyS]

Ahhh well not to worry! Wishful thinking. Seems as though Microsoft have improved Excel but not VBA!
I'll just have to reside myself to copy and paste each time instead.

Thanks anyway.

 

[markmzz]

Ahhh well not to worry! Wishful thinking. Seems as though Microsoft have improved Excel but not VBA!
I'll just have to reside myself to copy and paste each time instead.

Thanks anyway.

Did you see the post #21?

Markmzz

 

[BradleyS]

By the time I had pressed reply, you had provded the PERFECT solution, and it only works like a dream.

That's it I not pushing my luck anymore. You have been great thank you so much.

Everything at the click of one button, ITS AMAZING!!!!

 

[markmzz]

By the time I had pressed reply, you had provded the PERFECT solution, and it only works like a dream.

That's it I not pushing my luck anymore. You have been great thank you so much.

Everything at the click of one button, ITS AMAZING!!!!

I'm glad that the code helps and thanks for the feedback.

Markmzz