DashboardNovice
Member
In the attached file, my formulas are in the yellow cells. For each row, I want to return a statement indicating the proper Revenue Formula that should be used which is contingent upon the Contract Type.
In cell D7 I get the correct answer, but when I copy down I get the value error.
This appears to be due to the fact that the first SEARCH( ) is returning a #VALUE! error and because of this, the entire formula returns an error, even though the syntax for the nested IF would return a correct result on its own (see cell J8). So it's as if Excel isn't getting to the 2nd IF statement.
To solve this, I have to get Excel to ignore the first SEARCH( ) if that fails. I tried to use the IFERROR( ). I applied the entire formula inside of IFERROR( ) but that didn't work. I applied IFERROR( ) to the first SEARCH( ) and couldn't get that to work.
Is there any way to solve this without the IFERROR( )? If not, then what portion of the formula should the IFERROR( ) be applied to?
In cell D7 I get the correct answer, but when I copy down I get the value error.
This appears to be due to the fact that the first SEARCH( ) is returning a #VALUE! error and because of this, the entire formula returns an error, even though the syntax for the nested IF would return a correct result on its own (see cell J8). So it's as if Excel isn't getting to the 2nd IF statement.
To solve this, I have to get Excel to ignore the first SEARCH( ) if that fails. I tried to use the IFERROR( ). I applied the entire formula inside of IFERROR( ) but that didn't work. I applied IFERROR( ) to the first SEARCH( ) and couldn't get that to work.
Is there any way to solve this without the IFERROR( )? If not, then what portion of the formula should the IFERROR( ) be applied to?