| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nested IF statements - Help
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. -- If you can measure it, you can improve it! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested IF statements - Help
Try change the cell format of F1 to Text.
"Monomeeth" wrote: 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. -- If you can measure it, you can improve it! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested IF statements - Help
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" wrote: 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. -- If you can measure it, you can improve it! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested IF statements - Help
Thanks Joel, that solved it.
Much appreciation for your help! -- If you can measure it, you can improve it! "Joel" wrote: 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" wrote: 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. -- If you can measure it, you can improve it! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Nested IF statements - Help
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! "Joel" wrote: 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" wrote: 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. -- If you can measure it, you can improve it! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| NESTED IF STATEMENTS | New Users to Excel | |||
| nested if statements | Excel Worksheet Functions | |||
| Nested IF statements | Excel Discussion (Misc queries) | |||
| Nested If/Then statements | Excel Worksheet Functions | |||
| Help with Nested If Statements | Excel Discussion (Misc queries) | |||
Linear Mode
