Excel IF rules and Dependants

[funkylad20]

I want a cell to display a 'Y' or an 'N' in a column, dependent on what another two cells contain (one a birth date, another a term type).

I have the first part thanks to some help on here last Friday afternoon, and have the following formula:
=IF(B2<37865,"N",IF(B2>38595,"N","Y"))

...which basically says 'if a child is born between 1st September 2003 and the 31st August 2008, they are ['Y] eligible. If not, they are ['N'] not.
These dates are relevant to the AUTUMN TERM 2008.

NOW, I want these dates to change, dependent on what another cell says later in the spreadsheet. For example, if I am then talking about the SPRING TERM 2009, the dates need to be shifted. The sam principal applies but the dates need to change.

I have started it, hopefully...something like this seems on the right track?!:

IF((X2 "Autumn 2008")B2<37865,"N",IF(B2>38595,"N","Y")) - X2 is the cell with 'Autumn Term 2008' in. Alternatively it will be Spring Term 2009 or Summer Term 2009, so will need multiple coding putting in...

[kempie]

Taking into account your previous post...

http://www.theanswerbank.co.uk/Technology/Comp uters/Question607276.html

... to keep thing simple I would suggest that when you change cell X2 you set up two cells to indicate the Term start and end dates which can then be referenced by your age eligibility formula.

e.g.

Term start date cell (X3 or whatever)
=IF(R12="Autumn 2008",DATE(2003,9,1),IF(R12="Spring 2009",DATE(2004,1,1),IF(R12="Summer 2009",DATE(2004,5,1),"")))

(NB no spaces in formula except for Term descriptions in quotes)

Term end date cell (X4 or whatever)
=IF(R12="Autumn 2008",DATE(2008,8,31),IF(R12="Spring 2009",DATE(2008,12,31),IF(R12="Summer 2009",DATE(2009,4,31),"")))

then your age eligibilty formula can be simplified to
=IF(OR(B2<X3,B2>X4),"N","Y")

The use of DATE(y,m,d) removes your datecode complication.

[kempie]

^ Ummm...

I have transposed R12 for X2 in the above formulae (don't ask - i don't know why!)

[funkylad20]

Kempie, thank you! Works a treat.
One other thing - not a necessity, but if the cell with the DOB (B2 in your formula) is left blank, how do I make the other cell blank (with the 'age eligibility formula' in it) rather than a 'Y' or a 'N'?

Problem is, multiple following cells now contain 'Y' even though no record/DOB is entered...

[kempie]

Test if the cell is blank using ="" (empty quotes)
If blank return "" (empty quotes)
Otherwise use formula

=IF(B2="","",IF(OR(B2<X3,B2>X4),"N","Y"))

[funkylad20]

Perfect, many thanks!
Does that work for any cell then? I often wonder if there is a way, when entering ongoing data over a period of time, rather than just dragging the formulas downwards through the spreadsheet creating #VALUE! results that it simply displays as a blank cell...when mailmerging, it always treats the #VALUE! etc as entries and merges for thousands of pages!!

[funkylad20]

WOW! On testing it out, it appears it does...! haha! Am very impressed!! No more massive mail merge word documents!

[kempie]

Yes - to ensure any formula returns a blank cell whenever it encounters a blank cell as input, use the following style...

=IF(A1="","", {rest of formula} )