Nested if statement multiple sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2385054%22%20slang%3D%22en-US%22%3ENested%20if%20statement%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385054%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20create%20a%20nested%20IF%20statement%20as%20follows%2C%20the%20following%20formula%20works%20correctly%20and%20as%20expected%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%24D%242%3D%22points%22%2C%24C%245*%3CFONT%20color%3D%22%23FF6600%22%3E%3CSTRONG%3E%24G%247%3C%2FSTRONG%3E%3C%2FFONT%3E%2CIF(%24D%242%3D%22Discount%20or%20GWP%22%2C%24C%246%2CIF(%24D%242%3D%22Cash%20Reward%20or%20Cashback%22%2C%24C%247*%3CSTRONG%3E%3CFONT%20color%3D%22%23FF6600%22%3E%24G%247%3C%2FFONT%3E%3C%2FSTRONG%3E*100)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20when%20I%20try%20and%20use%20the%20following%20formula%2C%20the%20only%20change%20is%20the%20bit%20in%20Orange%2F%20Bold%20the%20formula%20then%20triggers%20and%20error%20and%20doesn't%20work%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DIF(%24C%242%3D%22Points%22%2C%24C%246*%3CFONT%20color%3D%22%23FF6600%22%3E%3CSTRONG%3E'Business%20Case%20Refs'!B2%3C%2FSTRONG%3E%3C%2FFONT%3E%2CIF(%24C%242%3D%22Discount%20or%20GWP%22%2C%24C%247%2CIF(%24C%242%3D%22Cash%20Reward%20or%20Cashback%22%2C%24C%249*'%3CFONT%20color%3D%22%23FF6600%22%3E%3CSTRONG%3EBusiness%20Case%20Refs'!B2%3C%2FSTRONG%3E%3C%2FFONT%3E*100)))%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20entirely%20sure%20why%20this%20doesn't%20carry%20over%2C%20the%20cells%20are%20all%20the%20same%20format%20(Currency)%20and%20the%20only%20obvious%20thing%20I%20can%20see%20if%20that%20the%20cell%20is%20on%20another%20sheet%20but%20I%20don't%20think%20that%20should%20cause%20a%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnything%20I%20can%20try%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2385054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385153%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statement%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061542%22%20target%3D%22_blank%22%3E%40jharrison266%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20formula%20is%20OK.%20Make%20sure%20that%20you%20have%20spelled%20the%20sheet%20name%20correctly.%20Could%20it%20perhaps%20be%20%22Business%20Case%20Refs%20%22%20(with%20a%20space%20at%20the%20end)%20instead%20of%20%22Business%20Case%20Refs%22%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385205%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statement%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385205%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061542%22%20target%3D%22_blank%22%3E%40jharrison266%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhich%20kind%20of%20error%20do%20you%20have%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormulas%20have%20bit%20different%20logic.%3C%2FP%3E%0A%3CP%3EFirst%3C%2FP%3E%0A%3CP%3E%3DIF(%24D%242%3D%22points%22%2C...%2CIF(%24D%242%3D...%2C%24C%246%2CIF(%24D%242%3D...%2C%24C%247*%3CFONT%20color%3D%22%23ff6600%22%3E%3CSTRONG%3E...%3C%2FSTRONG%3E%3C%2FFONT%3E)))%3C%2FP%3E%0A%3CP%3ESecond%3C%2FP%3E%0A%3CP%3E%3DIF(%24C%242%3D%22Points%22%2C...%2CIF(%24C%242%3D...%2C%24C%247%2CIF(%24C%242%3D...%2C%24C%249*'%3CFONT%20color%3D%22%23ff6600%22%3E%3CSTRONG%3E...%3C%2FSTRONG%3E%3C%2FFONT%3E)))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385217%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statement%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385217%22%20slang%3D%22en-US%22%3EI%20tried%20this%2C%20and%20changed%20the%20tab%20name%20to%20a%20single%20word%2C%20no%20luck.%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%20tried%20a%20simpler%20statement%20to%20see%20if%20it%20works%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(%24C%242%3D%22Points%22%2C%24C%246*References!B2)%3CBR%20%2F%3E%3CBR%20%2F%3ENo%20luck!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385231%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statement%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385231%22%20slang%3D%22en-US%22%3EHey%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20error%20is%20just%20%23Value!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2385378%22%20slang%3D%22en-US%22%3ERe%3A%20Nested%20if%20statement%20multiple%20sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2385378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061542%22%20target%3D%22_blank%22%3E%40jharrison266%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20wrong%20reference%20you'll%20have%20%23REF!%20error.%20With%20%23VALUE!%20it's%20something%20else%2C%20e.g.%20you%20try%20to%20multiply%20text%20on%20number.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi, 

 

I'm trying to create a nested IF statement as follows, the following formula works correctly and as expected:

 

=IF($D$2="points",$C$5*$G$7,IF($D$2="Discount or GWP",$C$6,IF($D$2="Cash Reward or Cashback",$C$7*$G$7*100)))

 

However when I try and use the following formula, the only change is the bit in Orange/ Bold the formula then triggers and error and doesn't work: 

 

=IF($C$2="Points",$C$6*'Business Case Refs'!B2,IF($C$2="Discount or GWP",$C$7,IF($C$2="Cash Reward or Cashback",$C$9*'Business Case Refs'!B2*100))) 

 

I'm not entirely sure why this doesn't carry over, the cells are all the same format (Currency) and the only obvious thing I can see if that the cell is on another sheet but I don't think that should cause a problem?

 

Anything I can try?

 

Thanks 

5 Replies

@jharrison266 

The formula is OK. Make sure that you have spelled the sheet name correctly. Could it perhaps be "Business Case Refs " (with a space at the end) instead of "Business Case Refs"?

@jharrison266 

Which kind of error do you have?

 

Formulas have bit different logic.

First

=IF($D$2="points",...,IF($D$2=...,$C$6,IF($D$2=...,$C$7*...)))

Second

=IF($C$2="Points",...,IF($C$2=...,$C$7,IF($C$2=...,$C$9*'...)))

I tried this, and changed the tab name to a single word, no luck.

Also tried a simpler statement to see if it works:

=IF($C$2="Points",$C$6*References!B2)

No luck!
Hey,

The error is just #Value!

@jharrison266 

With wrong reference you'll have #REF! error. With #VALUE! it's something else, e.g. you try to multiply text on number.