CONCAT or INDIRECT not working for dynamic formula

[bwinchell]

Hello,
I have used the INDIRECT in a bunch of workbooks to dynamically create formulas based on 1 or multiple cells. Usually to reference a worksheet tab name based on date or function.

A3 = 10012015 (cell formatted as text as I need leading zeros)

E.g.

=COUNTIFS(INDIRECT("VM_Audit_"&$A3&"!"&"$A$2:$A$2000"),"<>"&"",INDIRECT("VM_Audit_"&$A3&"!"&"$I$2:$I$2000"),"BC_ESXi_CLUST*")

This will translate into and work fine

=COUNTIFS(VM_Audit_10012015!$A$2:$A$2000,"<>",VM_Audit_10012015!$I$2:$I$2000,"BC_ESXi_CLUST*")

I am trying to do something very similar to count distinctive occurrences of text. I have tried the CONCAT & INDIRECT in this formula but neither will work. It logically makes sense but something is breaking.

Static formula that works

{=SUM(IF(VM_Audit_10012015!$J2:$J2000<>"",1/COUNTIF(VM_Audit_10012015!$J2:$J2000,VM_Audit_10012015!$J2:$J2000),0))}

Dynamic formula that does not work. Returns a #VALUE error:

{=SUM(IF((CONCATENATE("'VM_Audit_",$A3,"'!","$J2:$J2000","<>","""")),1/COUNTIF(VM_Audit_10012015!$J2:$J2000,VM_Audit_10012015!$J2:$J2000),0))}

Dynamic formula that does not work. Returns a #REF error:

{=SUM(IF(INDIRECT("VM_Audit_"&$A3&"!"&"$J$2:$J$2000"&"<>"&""""),1/COUNTIF(VM_Audit_10012015!$J2:$J2000,VM_Audit_10012015!$J2:$J2000),0))}

Any suggestions or assistance? I am stuck on this one and cannot figure out why this not working.


PS
Is there any way to copy the text out of the "evaluate formula" window? It is too small and I cannot see exactly how many commas are being entered.

PSS
If there are any Excel developers looking at this, please make the window re-sizable or simple copy/paste functionality

Thanks
B

 

[Domenic]

Try...

=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000")<>"",1/COUNTIF(INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000"),INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000"))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[bwinchell]

Try...

=SUM(IF(INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000")<>"",1/COUNTIF(INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000"),INDIRECT("'VM_Audit_"&$A3&"'!$J2:$J2000"))))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

That worked.
Thanks
B