0
votes

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;"<>")
1
Use Sheets(idx) reference where idx is location of Worksheet in tabsdbmitch
Sheet(A1) = 1 and not 'SheetA'locobastos

1 Answers

1
votes

You want INDIRECT():

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