A dynamic indirect function..?

[in_d]

Hi all,

I have tried to search for an answer, but with no luck!

In a spreadsheet I have a tab named 'ProjectsSummary' which holds a table called 'tbl_ProjectSummary'. Within that table, there is a column called [Project Name] which has a list of all the projects I am tracking.

Within the same workbook, each project has it's own worksheet, which is named exactly the same as the entry in 'tbl_ProjectSummary[Project Name]'. Within each worksheet, I have a named range called 'Resources' which lists the names of the resources who are working on the projects.

My desire is to create another worksheet with a summary of the resources which will show the number of projects they are working on, which I want to achieve through a combination of a sumproduct and indirect functions (sumproduct so that I can add some further criteria to the formula later).

I have started by using an indirect function to list all of the projects in the workbook using:

=INDIRECT("tbl_ProjectSummary[Project Name]")

which gives:

{"Project1";"Project2"}

I have no idea how to progress the next step which will then reference the 'Resources' named range on each of the project worksheets and check, using a sumproduct, if the name is present i.e.

=SUMPRODUCT(--(INDIRECT("tbl_ProjectSummary[Project Name]")[COLOR=#ff0000]&Resources[/COLOR])="Simon"))

=SUMPRODUCT(--({"[COLOR=#ff0000]Project1!Resources[/COLOR]";"[COLOR=#ff0000]Project2!Resources[/COLOR]"})="Simon"))

=SUMPRODUCT(--({[COLOR=#ff0000]"Simon";"Paul";"David";"John";"Simon";"John";"Greg";"Dave"[/COLOR]})="Simon"))

...

=2

Where the bits in red are what I am expecting to see! It all falls apart from me when I try to include the 'Resources' named range!

I thank you in advance for any assistance you can provide!!

 

[sunnyland]

Supposing in F8 you have Simon

=SUMPRODUCT(--(INDIRECT("project1!resources")=F8))+SUMPRODUCT(--(INDIRECT( "project2!resources")=F8))

will give the number of time simon appeared in resources on project1 and in resources on project2

The names resources have a worksheet scope .

Hope this is useful.

 

[Andrew Poulsom]

Does this work for you?

=SUMPRODUCT(COUNTIF(INDIRECT(tbl_ProjectSummary[Project Name]&"!Resources"),"Simon"))