+ Reply to Thread
Results 1 to 2 of 2

Using the INDIRECT function to reference anther worksheet

  1. #1
    Registered User
    Join Date
    06-30-2011
    Location
    Here
    MS-Off Ver
    Excel 2003
    Posts
    2

    Using the INDIRECT function to reference anther worksheet

    Help and explanation please

    I am using the formulae =SUMPRODUCT(INDIRECT("'"&M11&"'!"&($I$12:$I$20)="P")*(INDIRECT("'"&M11&"'!"&B$12:$B$20=($A12&$C12)))) to search through a series of worksheet to count how many times a vlaue is referenced.

    where I am trying to use the forumula to return the text string HubA value in cell M11 to reference a worksheet called 'HubA' then check the SUMPRODUCT conditions.

    The original formulae I use for to reference the worksheet works (below)

    =SUMPRODUCT((HubA!$I$12:$I$20="P")*(HubA!B$12:$B$20=($A13&$C13)))

    I admit I dont understand the INDIRECT formula and the syntax, especially all of the " and ' entry's

    Thanks

    martin

  2. #2
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Using the INDIRECT function to reference anther worksheet

    Hello,

    You are almost there, use this;

    =SUMPRODUCT((INDIRECT("'"&M11&"'!I12:I20")="P")*(INDIRECT("'"&M11&"'!B12:B20")=$A3&$C13))
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1