Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Referencing another worksheet in formula (97)

    <P ID="edit" class=small>(Edited by awckie on 02-Jul-03 18:19. Note that this did not paste as I wanted it to [img]/forums/images/smilies/smile.gif[/img] Imagine all the numbers in different columns with the headings in the row above!)</P>Hello...

    I have a summary tab in my workbook which looks like: (But formatted nicely [img]/forums/images/smilies/smile.gif[/img])

    Plan # OP UC Unsubmitted In Process Approved Total Units
    Anita 16 3 3 1 0 1 8
    John 11 1 2 5 0 3 11
    Joe 14 0 4 9 1 2 16
    Carol 14 5 2 7 2 1 17
    Smith 13 7 1 0 0 0 8
    Taylor 10 4 2 1 0 1 8
    Jen 13 3 1 6 0 2 12
    Frank 19 19 1 4 0 0 24
    Lou 10 2 1 3 0 0 6
    Jessie 14 6 2 7 0 1 16


    Each of the names in column A is the same name as a sheet in the workbook. Currently I am using CountIF formulas to get the summary numbers EX: =COUNTIF(Anita!G:G,"UC")

    Instead of having Anita in the formula I would like to have A3 (where Anita's name is in the current summay sheet) If I just put in A3, I get a File Not Found.

    Can I do what I want to do?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Referencing another worksheet in formula (97)

    It is literally looking for a file named A3 not a reference to what is IN A3. You must use the INDIRECT function:

    <pre>=COUNTIF(indirect("'" & A3 & "'!G:G"),"UC")</pre>


    Steve

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    San Clemente, California, USA
    Posts
    130
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Referencing another worksheet in formula (97)

    Thank you! Now I know about a new function!

    I sure do appreciate it..

Posting Permissions

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