INDIRECT informulas

[no_name]

I have a cell which has the code

=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.

I want to use this value in a COUNTIFS

what should the syntax be?


Thanks in advance




--------------= Posted using GrabIt =----------------
------= Binary Usenet downloading made easy =---------
-= Get GrabIt for free from http://www.shemes.com/ =-

 

[joeu2004]

I have a cell which has the code
=INDIRECT("'" & Summary!A18 & "'!" & "A23") in it.
I want to use this value in a COUNTIFS
what should the syntax be?

Perhaps:

COUNTIFS(INDIRECT("'" & Summary!A18 & "'!" & "A1:A1000"),B1,C1:C1000,D1)

or

COUNTIFS(B1:B1000,INDIRECT("'" & Summary!A18 & "'!" & "A23"),C1:C1000,D1)

Provide a concrete example that explains what you want to do. The more
specific your question, the more specific the answer.

But in any case, be careful what you wish for.

Since INDIRECT is a volatile function, your COUNTIFS formula will be
recalculated, along with any directly or indirectly dependent formulas,
every time you edit __any__ cell in __any__ worksheet in the workbook.

This can be slow things down significantly, especially if you use
whole-column references (not recommended) in the COUNTIFS formula, like:

COUNTIFS(B:B,INDIRECT("'" & Summary!A18 & "'!" & "A23"),C:C,D1)