Excel COUNTA in a different sheet using more than one word sheet name

0
votes

I have a workbook with 4 worksheets:

  1. Table of Contents
  2. ABC
  3. DEF
  4. GH IJK

I want to count in the table of contents the number of items I have in the other sheets. all the items are in column A so I need to count A:A-1

Here is my worksheet

as you can see I have in column D the items that I should see (I entered them manually) and on column C I tried a formula that worked only on sheets that has one word.

I have a User Defined function that is called Sheet_Name_From_Number(), I put a number, let's say 2, and the function returns the name of sheet number 2, in our example it will return ABC

so this is my formula:

=COUNTA(INDIRECT(PERSONAL.XLSB!Sheet_Name_From_Number(A2)&"!A:A"))-1

Here is the formula in cell C2

I'm pretty stuck as I don't know how to count column A from these more than one word sheets. any help will be much appreciated.

2
excelvbafunctionexcel-formula
Can you please include your UDF function? – dwirony

2 Answers

1
votes

Add single quotes around the sheet name:

=COUNTA(INDIRECT("'" & PERSONAL.XLSB!Sheet_Name_From_Number(A2) & "'!A:A"))-1
1
votes

The other answers are perfectly fine, but the "proper" way to do it would be to include the single quotes in your UDF.

The last lines of it are probably something like

    ...
    Sheet_Name_From_Number = something
End Function

Replace that with

    ...
    Sheet_Name_From_Number = "'" & something & "'"
End Function

and then you can keep your formulas as is.