Hi, I'm currently using (probably) a long formula to display data by
day/month(below) via a validation list (cell B2)but when I amend the formula
to 31 days for Dec I get the error message that cell C72 has a formula that
can't be converted because it contains a function not available in file
format to which I'm saving(Excel 97-2000). The only extra text added is
IF($B$2="31st",AH5,without this everything works fine. Any help greatly
appreciated.

=IF($B$2="1st",Data!D5,IF($B$2="2nd",Data!E5,IF($B $2="3rd",Data!F5,IF($B$2="4th",Data!G5))))+IF($B$2 ="5th",Data!H5,IF($B$2="6th",Data!I5,IF($B$2="7th" ,Data!J5,IF($B$2="8th",Data!K5))))+IF($B$2="9th",D ata!L5,IF($B$2="10th",Data!M5,IF($B$2="11th",Data! N5,IF($B$2="12th",Data!O5))))+IF($B$2="13th",Data! P5,IF($B$2="14th",Data!Q5,IF($B$2="15th",Data!R5,I F($B$2="16th",Data!S5))))+IF($B$2="17th",Data!T5,I F($B$2="18th",Data!U5,IF($B$2="19th",Data!V5,IF($B $2="20th",Data!W5))))+IF($B$2="21st",Data!X5,IF($B $2="22nd",Data!Y5,IF($B$2="23rd",Data!Z5,IF($B$2=" 24th",Data!AA5))))+IF($B$2="25th",Data!AB5,IF($B$2 ="26th",Data!AC5,IF($B$2="27th",Data!AD5,IF($B$2=" 28th",Data!AE5))))+IF($B$2="29th",Data!AF5,IF($B$2 ="30th",Data!AG5,IF($B$2="31st",Data!AH5,IF($B$2=" Month",SUM(Data!D5:AH5,0)))))

IF can have only 7 nested functions for all versions of XL prior to XL 2007.

You may have better luck using VLOOKUP.


--
Brevity is the soul of wit.


"Alun" wrote:

Hi, I'm currently using (probably) a long formula to display data by
day/month(below) via a validation list (cell B2)but when I amend the formula
to 31 days for Dec I get the error message that cell C72 has a formula that
can't be converted because it contains a function not available in file
format to which I'm saving(Excel 97-2000). The only extra text added is
IF($B$2="31st",AH5,without this everything works fine. Any help greatly
appreciated.

=IF($B$2="1st",Data!D5,IF($B$2="2nd",Data!E5,IF($B $2="3rd",Data!F5,IF($B$2="4th",Data!G5))))+IF($B$2 ="5th",Data!H5,IF($B$2="6th",Data!I5,IF($B$2="7th" ,Data!J5,IF($B$2="8th",Data!K5))))+IF($B$2="9th",D ata!L5,IF($B$2="10th",Data!M5,IF($B$2="11th",Data! N5,IF($B$2="12th",Data!O5))))+IF($B$2="13th",Data! P5,IF($B$2="14th",Data!Q5,IF($B$2="15th",Data!R5,I F($B$2="16th",Data!S5))))+IF($B$2="17th",Data!T5,I F($B$2="18th",Data!U5,IF($B$2="19th",Data!V5,IF($B $2="20th",Data!W5))))+IF($B$2="21st",Data!X5,IF($B $2="22nd",Data!Y5,IF($B$2="23rd",Data!Z5,IF($B$2=" 24th",Data!AA5))))+IF($B$2="25th",Data!AB5,IF($B$2 ="26th",Data!AC5,IF($B$2="27th",Data!AD5,IF($B$2=" 28th",Data!AE5))))+IF($B$2="29th",Data!AF5,IF($B$2 ="30th",Data!AG5,IF($B$2="31st",Data!AH5,IF($B$2=" Month",SUM(Data!D5:AH5,0)))))

Thanks Dave, I'll have a re-think.

"Dave F" wrote:

IF can have only 7 nested functions for all versions of XL prior to XL 2007.

You may have better luck using VLOOKUP.


--
Brevity is the soul of wit.


"Alun" wrote:

Hi, I'm currently using (probably) a long formula to display data by
day/month(below) via a validation list (cell B2)but when I amend the formula
to 31 days for Dec I get the error message that cell C72 has a formula that
can't be converted because it contains a function not available in file
format to which I'm saving(Excel 97-2000). The only extra text added is
IF($B$2="31st",AH5,without this everything works fine. Any help greatly
appreciated.

=IF($B$2="1st",Data!D5,IF($B$2="2nd",Data!E5,IF($B $2="3rd",Data!F5,IF($B$2="4th",Data!G5))))+IF($B$2 ="5th",Data!H5,IF($B$2="6th",Data!I5,IF($B$2="7th" ,Data!J5,IF($B$2="8th",Data!K5))))+IF($B$2="9th",D ata!L5,IF($B$2="10th",Data!M5,IF($B$2="11th",Data! N5,IF($B$2="12th",Data!O5))))+IF($B$2="13th",Data! P5,IF($B$2="14th",Data!Q5,IF($B$2="15th",Data!R5,I F($B$2="16th",Data!S5))))+IF($B$2="17th",Data!T5,I F($B$2="18th",Data!U5,IF($B$2="19th",Data!V5,IF($B $2="20th",Data!W5))))+IF($B$2="21st",Data!X5,IF($B $2="22nd",Data!Y5,IF($B$2="23rd",Data!Z5,IF($B$2=" 24th",Data!AA5))))+IF($B$2="25th",Data!AB5,IF($B$2 ="26th",Data!AC5,IF($B$2="27th",Data!AD5,IF($B$2=" 28th",Data!AE5))))+IF($B$2="29th",Data!AF5,IF($B$2 ="30th",Data!AG5,IF($B$2="31st",Data!AH5,IF($B$2=" Month",SUM(Data!D5:AH5,0)))))