Aug 02 2022 07:32 AM
My formula is not working... IF both C6 & C7 are TRUE (meaning B6 & B7 contain text "y") then I want the formula to pull in the number from cell H7 in the Water Rate Sheet. How can I fix the formula to work properly? Thanks for the help!
=IF($C$6=TRUE,C11*'Water Rate Sheet'!F7,IF($C$7=TRUE,C11*'Water Rate Sheet'!G7,IF(AND(C6=TRUE,C7=TRUE),C11*'Water Rate Sheet'!H7,C11*'Water Rate Sheet'!E7)))
The other version I've tried is:
=IF($C$6=TRUE,C11*'Water Rate Sheet'!F7,IF($C$7=TRUE,C11*'Water Rate Sheet'!G7,IF(AND(B6="y",B7="y"),C11*'Water Rate Sheet'!H7,C11*'Water Rate Sheet'!E7)))
Aug 02 2022 08:02 AM
In your screenshot, C6 and C7 are empty.
Apart from that, you should test for both B6 and B7 being "y" before the individual tests.
Or:
=C11*IF($B$6="y", IF($B$7="y", 'Water Rate Sheet'!H7, 'Water Rate Sheet'!F7), IF($B$7="y", 'Water Rate Sheet'!G7, 'Water Rate Sheet'!E7))
Aug 02 2022 09:12 AM - edited Aug 02 2022 09:22 AM
Thank you for the suggestion!
Yes, they work individually but not when I combine them in the formula, so my syntax must be wrong and it's just not obvious to me what is the error:
=IF($B$6="y",C11*'Water Rate Sheet'!F7,
IF($B$7="y",C11*'Water Rate Sheet'!G7,
IF($B$6="y",IF($B$7="y",C11*'Water Rate Sheet'!H7,
C11*'Water Rate Sheet'!E7))))
Aug 02 2022 10:19 AM - edited Aug 02 2022 10:20 AM
The problem with your formula is that if B6 equals "y", the first condition is true, so the formula returns C11*'Water Rate Sheet'!F7 and ignores the rest, so the part IF($B$6="y",IF($B$7="y" is not evaluated.
The formula that I posted takes care of that. (Please note that it is a single formula)
Aug 02 2022 10:27 AM