Indirect In formulas
 

Apologies for not making myself clear.

I am using a macro to populate column A with the names of all the worksheets in the workbook onto a sheet called Summary

I then want to use the names of the worksheets in a COUNTIF statement

so rather than =COUNTIF(G12:G889,"text")

I want to COUNTIF(INDIRECT("'" & Summary!A18 & "'!" & "A23"):=INDIRECT("'" & Summary!A18 & "'!" & "A24"),"text") whe

Summary!A18 is the other worksheet name and A23 and A24 are the neginnings and ends of the range.

Is that clearer?

Thanks in advance




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

Indirect In formulas
 

Hi,

Am Sun, 6 Oct 2013 14:09:23 +0100 schrieb no_name:

Summary!A18 is the other worksheet name and A23 and A24 are the neginnings and ends of the range.

if A23 and A24 are into the same sheet as your formula then:
=COUNTIF(INDIRECT("'"&Summary!A18&"'!"&A23&":"&A24 ),"text")

If A23 and A24 are also into sheet "Summary" then:
=COUNTIF(INDIRECT("'"&Summary!A18&"'!"&Summary!A23 &":"&Summary!A24),"text")


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2