Thread: 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