Oct 20 2021 07:37 AM
Hello All,
I am hoping someone can assist with this. I've evaluated this formula inside and out and I can't figure out why this error is being returned. I've checked the values within the cells to see if there are any spaces or special characters and there are none. I've even resorted to changing the return response from relating to a cell (which I find easier) to placing the response in quotes.
Here is the original formula:
=IF(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",""),
IF(AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,""),
IF(AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,""),
IF(AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,"")
I modified it to this thinking the return response values in the cells were causing the error but this still results in the #Value error:
=IF(AND(D8='Data Sheet'!A10,Log!E8='Data Sheet'!B10),"",""),
IF(AND(Log!D8='Data Sheet'!A11,Log!E8='Data Sheet'!B11),"EnterContactDate",""),
IF(AND(Log!D8='Data Sheet'!$A$12,Log!E8='Data Sheet'!$B$11),"EnterFollowUpDate",""),
IF(AND(Log!D8='Data Sheet'!$A$13,Log!E8='Data Sheet'!$B$11),"ChooseNextMethod","")
I've included the file for reference which is very basic because the log is in it's beginning production stage.
Can someone please let me know what is wrong here? I would greatly appreciate it.
Oct 20 2021 07:57 AM
Your issue is that each line is a complete IF() statement meaning you have multiple INDEPENDANT formulas in a single cell. The cell must contain ONE (albeit it can be complicated) formula. Another words the formula can have cascading IF statements that each get checked if the prior one is false. OR this is a good use of the IFS() statement:
=IFS(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",
AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,
AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,
AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,
TRUE,"")
notice how in each case I have REMOVED the ,"" at the end of each original IF statement because that would be the result in the FALSE case but instead you want it to do another IF case. Using the IFS() statement I could also remove the IF() for each if the successive conditions.
Oct 20 2021 08:05 AM
Oct 20 2021 08:16 AM
Solution@Michael1105 It was added in 2019 version. here is the same thing with cascading IF()
=IF(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",
IF(AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,
IF(AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,
IF(AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,
,""))))
notice I just added IF( at the start of those lines and all of the closing ) at the end
Oct 20 2021 08:33 AM
Oct 20 2021 08:16 AM
Solution@Michael1105 It was added in 2019 version. here is the same thing with cascading IF()
=IF(AND(D8='Data Sheet'!A4,Log!E8='Data Sheet'!B4),"",
IF(AND(Log!D8='Data Sheet'!A5,Log!E8='Data Sheet'!B5),'Data Sheet'!C4,
IF(AND(Log!D8='Data Sheet'!$A$6,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C6,
IF(AND(Log!D8='Data Sheet'!$A$7,Log!E8='Data Sheet'!$B$5),'Data Sheet'!C7,
,""))))
notice I just added IF( at the start of those lines and all of the closing ) at the end