Nested IF statements - Help

[Monomeeth]

Hi Everyone,

I need help with this formula. Can't seem to figure out what I'm doing wrong:

=IF((F1="1 April 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$E$22,IF((F1="1 May 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$F$22,IF((F1="1 June 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$G$22,"")))

Basically, what I want the formula to do is to check cell F1 to see what
date is entered and to then return the value contained in the appropriate
cell in Sheet1 of the Dummy Forecast AprMayJun 2008 workbook.

At present the formula returns a blank value regardless of what F1 contains
in it, but it should be returning either $35,500 , $0, or $119,000.

What am I doing wrong? And once this is fixed, can I extend the formula so
that I can cater for all twelve months, or is that stretching the nesting
capability too far?

Thanks.

 

[aushknotes]

Try change the cell format of F1 to Text.

 

[Joel]

The dates in the spreadsheet are probably a Serial Date and not a string.
When you put double quotes around the date is becomes a string. You need to
convert the string to a serial date by using the function datavalue().

=IF(F1=DateValue("1 April 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$E$22,IF(F1=DateValue("1 May 2008"),'[Dummy Forecast
AprMayJun 2008.xls]Sheet1'!$F$22,IF(F1=DateValue("1 June 2008"),'[Dummy
Forecast AprMayJun 2008.xls]Sheet1'!$G$22,"")))

 

[Monomeeth]

Thanks Joel, that solved it.

Much appreciation for your help!

--
If you can measure it, you can improve it!

The dates in the spreadsheet are probably a Serial Date and not a string.
When you put double quotes around the date is becomes a string. You need to
convert the string to a serial date by using the function datavalue().

=IF(F1=DateValue("1 April 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$E$22,IF(F1=DateValue("1 May 2008"),'[Dummy Forecast
AprMayJun 2008.xls]Sheet1'!$F$22,IF(F1=DateValue("1 June 2008"),'[Dummy
Forecast AprMayJun 2008.xls]Sheet1'!$G$22,"")))

Hi Everyone,

I need help with this formula. Can't seem to figure out what I'm doing wrong:

=IF((F1="1 April 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$E$22,IF((F1="1 May 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$F$22,IF((F1="1 June 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$G$22,"")))

Basically, what I want the formula to do is to check cell F1 to see what
date is entered and to then return the value contained in the appropriate
cell in Sheet1 of the Dummy Forecast AprMayJun 2008 workbook.

At present the formula returns a blank value regardless of what F1 contains
in it, but it should be returning either $35,500 , $0, or $119,000.

What am I doing wrong? And once this is fixed, can I extend the formula so
that I can cater for all twelve months, or is that stretching the nesting
capability too far?

Thanks.

 

[Monomeeth]

Oh, one more thing...

Will this work if I develop the workbook to reflect on twelve months worth
of data? Or would that be nesting too many IF statements?

*scratching head*

--
If you can measure it, you can improve it!

The dates in the spreadsheet are probably a Serial Date and not a string.
When you put double quotes around the date is becomes a string. You need to
convert the string to a serial date by using the function datavalue().

=IF(F1=DateValue("1 April 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$E$22,IF(F1=DateValue("1 May 2008"),'[Dummy Forecast
AprMayJun 2008.xls]Sheet1'!$F$22,IF(F1=DateValue("1 June 2008"),'[Dummy
Forecast AprMayJun 2008.xls]Sheet1'!$G$22,"")))

Hi Everyone,

I need help with this formula. Can't seem to figure out what I'm doing wrong:

=IF((F1="1 April 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$E$22,IF((F1="1 May 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$F$22,IF((F1="1 June 2008"),'[Dummy Forecast AprMayJun
2008.xls]Sheet1'!$G$22,"")))

Basically, what I want the formula to do is to check cell F1 to see what
date is entered and to then return the value contained in the appropriate
cell in Sheet1 of the Dummy Forecast AprMayJun 2008 workbook.

At present the formula returns a blank value regardless of what F1 contains
in it, but it should be returning either $35,500 , $0, or $119,000.

What am I doing wrong? And once this is fixed, can I extend the formula so
that I can cater for all twelve months, or is that stretching the nesting
capability too far?

Thanks.