 # 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

# Re: Nested if statement multiple sheets

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"?

# Re: Nested if statement multiple sheets

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*'...)))

# Re: Nested if statement multiple sheets

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!

# Re: Nested if statement multiple sheets

Hey,

The error is just #Value!

# Re: Nested if statement multiple sheets

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