Creating nested IFs with variables?

[thefishpatrol]

I'm creating a whole mess of charts to track incidents for the first 9
months of this year, based on a varying number of conditions. Editing
each formula by hand will get the job done, but there must be a way to
do this faster. *Is it possible, within a formula, to grab additional
bits of formula from another cell?*

For example, I have five columns of data: Month, Place, Type, Type
Description, and Severity. Right now I'm just nesting IFs, like so:

Code:
--------------------
=SUM(IF('Quarter1'!A1:A400="January",IF('Quarter1'!D1400="Bad",1,0)))

=SUM(IF('Quarter1'!A1:A400="January",IF('Quarter1'!D1400="4",IF('Quarter1'!B1:B400="Spill",1,0))))

=SUM(IF('Quarter1'!A1:A400="January",IF('Quarter1'!D1400="4",IF('Quarter1'!B1:B400="Spill",IF('Quarter1'C1:C400="Milk",1,0))))
--------------------

Basically, I have to create a chart based on every possible
combination, with 2-5 IFs. Pasting in each IF is laborious. *Is there a
way I can replace, say, "IF('Quarter1'!B1:B400="Spill" with
"'Definitions'!$A1" where 'Definitions'!$A1 =
IF('Quarter1'!B1:B400="Spill"?* That would make constructing the
formulas much, much faster.

 

[Niek Otten]

Take a look at the INDIRECT function. If you still have problems, write
again!

--
Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Don Guillett]

Have a look at the INDIRECT function.

I'm creating a whole mess of charts to track incidents for the first 9
months of this year, based on a varying number of conditions. Editing
each formula by hand will get the job done, but there must be a way to
do this faster. *Is it possible, within a formula, to grab additional
bits of formula from another cell?*

For example, I have five columns of data: Month, Place, Type, Type
Description, and Severity. Right now I'm just nesting IFs, like so:

Code:
--------------------
=SUM(IF('Quarter1'!A1:A400="January",IF('Quarter1'!D1400="Bad",1,0)))

=SUM(IF('Quarter1'!A1:A400="January",IF('Quarter1'!D1400="4",IF('Quarter1'
!B1:B400="Spill",1,0))))
=SUM(IF('Quarter1'!A1:A400="January",IF('Quarter1'!D1400="4",IF('Quarter1'
!B1:B400="Spill",IF('Quarter1'C1:C400="Milk",1,0))))
--------------------

Basically, I have to create a chart based on every possible
combination, with 2-5 IFs. Pasting in each IF is laborious. *Is there a
way I can replace, say, "IF('Quarter1'!B1:B400="Spill" with
"'Definitions'!$A1" where 'Definitions'!$A1 =
IF('Quarter1'!B1:B400="Spill"?* That would make constructing the
formulas much, much faster.

 

[thefishpatrol]

Using INDIRECT to add an IF statement returns #REF!
For example:

Code:
--------------------
IF('Quarter1'!$E1:$E500="Spill",IF('Quarter1'!$D1:$D500="Pantry",1,0)) [in cell A1 of worksheet "Home"]

=SUM(IF('Q1'!$B$1:$B$500="January",IF('Q1'!$G$1:$G$500="1",INDIRECT('Home'!$A1))))
--------------------

Tried moving the IF outside of the INDIRECT call with the same results.
IF(INDIRECT('Home'!$A1))=Volatile. Also, this doesn't really make my
job easier. What I really want to do is enter this:

Code:
--------------------
=SUM(IF(A,IF(B,IF(C,IF(D,1,0)))))
where
A='Quarter1'!$E1:$E500="Spill"
B='Quarter1'!$D1:$D500="Pantry"
C='Quarter1'!$B1:$B500="January"
etc.