Conditional Formulas

  • Thread starter Thread starter Excel Rookie
  • Start date Start date
E

Excel Rookie

I am working on a workbook where I would like to use a different formula
depending on the result of a cell. This without using an if/then statement.

That is, if the result of sheet2.C1 > 0, then the formula in A1 would be "=K5"
If the result of sheet2.C1 = 0, then the formula in A1 would be "=S5"

I can't use a simple if/then statement such as if('sheet2'!C1 > 0, K5, S5)
because sheet2 may be deleted. Once sheet2 is deleted, the formula would
cause #REF error.

I thought of using Scenarios but not sur how that works. Is than any way to
accomplish this without using an if/then statement?

Thanks.
 
You can still use the IF() function:

=IF(ISERR(INDIRECT("Sheet2!C1")),"",IF(INDIRECT("Sheet2!C1")>0,K5,S5))
 
Thanks for the suggestion. However, it did not work for me. My formula
(simplified) is...
=IF(ISERR(INDIRECT('Sheet2'!C1)),'Sheet1'!K5,'Sheet2'!S5)

But when I delete Sheet2, I get...
=IF(ISERR(INDIRECT(#REF!C1)),'Sheet1'!K5,#REF!S5)

I need a way to do this so that it would still work after I delete Sheet2 or
Sheet1.
 
This is just an expanded version of what RD wrote -- putting more "isErr"
functions around.

I have (maybe) a Sheet1 and a Sheet2. I do have a Sheet4. In Sheet4 I have
the following in cells A1:A3:

A1: Sheet1!K5
A2: Sheet2!S5
A3: Sheet1!C1

In A6 (on Sheet4) put the following:

=IF(ISERR(INDIRECT(A3)),"",IF(INDIRECT(A3)>0,IF(ISERR(INDIRECT(A1)),"",INDIRECT(A1)),IF(ISERR(INDIRECT(A2)),"",INDIRECT(A2))))

Now, this assumes that you want "blanks" if Sheet1 is missing. If Sheet1 is
there, and the formula needs Sheet2, it also assumes you want "blank" if
Sheet2 is missing. It also assumes that the value in Sheet1:C1 is never
negative.

These special conditions can be fixed... the formula just gets messier.

Good luck.
 
This is an addition to the material from RD:

On Sheet4 in A1:A3 I have:
A1: Sheet1!K5
A2: Sheet2!S5
A3: Sheet1!C1

In cell A6 on Sheet4 I have
=IF(ISERR(INDIRECT(A3)),"",IF(INDIRECT(A3)>0,IF(ISERR(INDIRECT(A1)),"",INDIRECT(A1)),IF(ISERR(INDIRECT(A2)),"",INDIRECT(A2))))

I think this does what you want, or at least close to it. It does put in
blanks if sheets don't exist and it does assume that Sheet1:C1>=0. These can
be dealt with if that doesn't work.
 
Back
Top