Count active rows in a remote WS

Halia
Halia used Ask the Experts™
on
Hello,

I need to be able to count the active rows (A:A) in a remote worksheet.  There are about 75 of them.

I have this formula working...

=COUNTA('C:\Xref_Files\\[XRef 1.xlsx]XREF_1!A:A)

But I was hoping to just Concatinate the string string based on columns instead of typing the formula each time - such as...

C:\Xref_Files\      [XREF 1.xlsx]      Sheet1!      A:A
C:\Xref_Files\      [XREF 2.xlsx]      Sheet1!      A:A
C:\Xref_Files\      [XREF 3.xlsx]      Sheet1!      A:A

And build the formula based off of this.  Any ideas?

Thanks,

Trish
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Please try a formula like this:

=COUNTA(INDIRECT("'" &A1&B1&C1&D1))

Take a look at the attached example.

jppinto

PS: Please note that the 3rd column cannot be like this:

Sheet1!

but need to be like this:

Sheet1'!
Indirect-COUNTA.xlsx

Author

Commented:
Thanks, I changed the column C to Sheet1'! .  When I plug in that formula it is returning a value of 1 when I should see 15.

Author

Commented:
I got it fixed using your formula!  They are naming the tabs!  When i changed it to the name of the tab it works great.

Thank you!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial