Sumifs 3-D Cell Reference Question COMPLICATED

[p4r4norm4l]

Hey guys,
I'm in a bit of a head scratching loop right now
I will try to explain as much as i can and as clearly as i can so please bear with me on this one.

I have a workbook with worksheets from January to December which are there for organizing purposes only.

I have this other Worksheet Which is used as a template called "Template_Evenement" Which i use to enter Data inside for each event i do during the months and when i'm done entering the data inside i make a copy of it and place it between the corresponding month where it will occur and i also have another work sheet which i want to automagically update itself as a report system from the data from the copied Worksheet "Template_Evenement" (will be a different name everytime ex: Contract Number-Date of event (C06111-1-02-2016))

The report is a little complicated

this is what works on the same worksheet (TEMPLATE_EVENEMENT)

=SUMIFS(D8,I5,"Patrick Brochu",B5,"Corporatif")

On the report worksheet how would i tell it to get the values from D8 (Template_evenement) based on the Dropdown item of I5 and the Dropdown item of B5 between the worksheets January and February ... and when i add sheets it automagically adds them to the report ?

 

[p4r4norm4l]

Should i reference a couple of sheets in advance for this to work with sumproduct and indirect ?

 

[steve the fish]

Im a bit confused by what you wrote but maybe if I give you the syntax you can get it to work:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Months&"'!D8"),INDIRECT("'"&Months&"'!I5"),"Patrick Brochu",INDIRECT("'"&Months&"'!B5"),"Corporatif"))

where Months is a named range containing your sheet names.

 

[p4r4norm4l]

Im a bit confused by what you wrote but maybe if I give you the syntax you can get it to work:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&Months&"'!D8"),INDIRECT("'"&Months&"'!I5"),"Patrick Brochu",INDIRECT("'"&Months&"'!B5"),"Corporatif"))

where Months is a named range containing your sheet names.

Thanks Steve the Fish for the quick Response.

I know the syntax you gave me will work but what's complicated about my problem is that i always add new sheets between January and February, this means that the sheets are not added to the range. Does that mean i have to add them in the range everytime i add a sheet or can it be done automatically through VBA?

 

[p4r4norm4l]

Maybe this will help

This image is the report
We will use C2 as the example cell here.

So basically, i want the report to get the data in Template_evenement(see image below)

So get data from D8 (highlighted in blue) if I5 is equal to Patrick Brochu and if B5 is equal to Corporatif Between the sheets of Janvier and Février.(image below).

Even though there is not anything between Janvier & Février in the image, i want the report to automatically update itself evertime i add a copy of the template between Janvier & Février which contains the data that i want.

The template_evenement is a sheet where i enter data, copy the template between the appropriated months, rename it and delete the data from the template afterwards.

Hope this helps

 

[p4r4norm4l]

Maybe this will help

This image is the report
We will use C2 as the example cell here.

So basically, i want the report to get the data in Template_evenement(see image below)

So get data from D8 (highlighted in blue) if I5 is equal to Patrick Brochu and if B5 is equal to Corporatif Between the sheets of Janvier and Février.(image below).

Even though there is not anything between Janvier & Février in the image, i want the report to automatically update itself evertime i add a copy of the template between Janvier & Février which contains the data that i want.

The template_evenement is a sheet where i enter data, copy the template between the appropriated months, rename it and delete the data from the template afterwards.

Hope this helps

Anyone have an idea how to do this