Forum Discussion
Nested if statement multiple sheets
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
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*'...)))
- Hey,
The error is just #Value!With wrong reference you'll have #REF! error. With #VALUE! it's something else, e.g. you try to multiply text on number.
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"?
- 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!
