Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    CountA formula (EXCEL XP & 2003)

    Can you insert a tab name in a formula? In the attachment , there is an excel workbook. Column A has the same names as the worksheets tab names respectively. can the formula in column C use variable for the worksheet tab name and copy the formula to the next row to achieve the formula resuts for that specific worksheet.

    Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CountA formula (EXCEL XP & 2003)

    You can use the INDIRECT function for this purpose. Enter the following formula in C3:
    <code>
    =COUNTA(INDIRECT("'"&A3&"'!F4:F500"))
    </code>
    You can copy this formula to the other cells in column C.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CountA formula (EXCEL XP & 2003)

    The formula works but it is not relative and doesn't move down the rows. Is it possible to make it relative formula.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CountA formula (EXCEL XP & 2003)

    The reference to the cell in column A is relative - if you copy the formula from C3 to C5, it will refer to A5 instead of A3.
    Surely you don't want the reference to F4:F500 on the other sheet to be relative?

  5. #5
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: CountA formula (EXCEL XP & 2003)

    Thank you

Posting Permissions

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