concatenate text with formulas in Excel 2013

Question

I have this kind of excel datasheets :

Sheet0 :

    column A     column B
 1  SheetA       =COUNTIF('SheetA'!A:A;"<>")
 2  SheetB       =COUNTIF('SheetB'!A:A;"<>")
 3  SheetC       =COUNTIF('SheetC'!A:A;"<>")
 .  ... More than 60 rows ...

My sheets name are based on the column A with this VBA function :

Sub SheetRenames()
    Dim R As Range
    Dim WS As Worksheet
    Set R = ActiveCell
    For Each WS In ThisWorkbook.Worksheets
        WS.Name = R.Value
        Set R = R(2, 1)
    Next WS
End Sub

I would like, in my formula, to replace the raw text 'SheetA' by the content of the column A because I can modify the sheets name and I don't want to fix all the formulas...

Is it possible ? If yes, how.

Thx in advance.

EDIT :

CELL("contents";A1) = SheetA

Now what is the good syntax ?

=COUNTIF('CELL("contents";A1)'!A:A;"<>")

Use Sheets(idx) reference where idx is location of Worksheet in tabs — dbmitch

Scott Craner Scott Craner · Accepted Answer · 2016-06-13T22:16:37

1

votes

You want INDIRECT():

=COUNTIF(INDIRECT("'" & A1 & "'!A:A"),"<>")

concatenate text with formulas in Excel 2013

1 Answers