Results 1 to 2 of 2

Thread: COUNTA cell reference question

  1. #1
    Neophyte Chrispy's Avatar
    Join Date
    Nov 2019
    Posts
    1
    Articles
    0
    Excel Version
    2010

    COUNTA cell reference question



    Register for a FREE account, and/
    or Log in to avoid these ads!

    good morning!

    i have a workbook with many, many sheets. each sheet corresponds to a site name with an obscure name such as cecsumbax, cecbrkph, pedensum, etc...

    i need to create a summary page, which gives on one line, the site name, and the number of entries(rows) on that page.

    example:

    CECANSTM 45
    CECOSASC 7
    CECOSASP 4
    CECOSAUD 1
    CECOSBRD 1
    CECOSCBH 2
    CECOSCIR 2

    i am using the formula :

    =COUNTA(CECANSTM!$A$2:CECANSTM!$A$9999)

    which works fine, but requires me to manually type the site/sheet name on each line.. twice...

    is there a way to write the forumula so it references the value in the cell next to it as the worksheet name so i can just cut and paste all the way down? basically i want to replace cecanstm in the above formula with 'g1' but i havent found syntax that works.

    thanks in advance


    Last edited by Chrispy; 2019-11-23 at 09:52 PM.

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,680
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Perhaps
    =COUNTA(indirect("'"&g1&"'!"&"$A$2:$A$9999")
    and pull down as needed

    BTW in your formula you did not have to reference the sheetname twice =COUNTA(CECANSTM!$A$2:$A$9999)
    Thank you Ken for this secure forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •