Reference data withinn a specific time period

Hello All.

First of all thank you for your patience. I am working on becoming a macro wizard like most of you all on here!

I have attached an example workbook that will make it much easier to understand the vision that I am looking for help on.

In the “Data” worksheet there is data on how long each step is taking along with the date opened and closed. With this the “Days Open” is calculated at the bottom row(row 15).
My goal is to able to write any month within the tables in the “Data Interpretation” sheet (Insert first/last day of month) which will only reference the date opened of the “Data” sheet (row 4). All columns that then become applicable their “Days Open” (row 15) will be averaged which will then provide the value in “Result to be shown below” (row Y8 of “Data Interp” sheet). And then in the “Department Specific Calculator” there will two conditions, the first being the same as the other table - the date opened must fall between the dates specified by the individual AND THEN secondly the department name must match and ultimately giving an average, of only those specific columns with the two conditions, of their days opened.

Please do let me know if it is hard to follow and I will figure out a different way to explain myself.

Thank you all very much whatever help you may provide I will be very grateful. It is insane how effective and efficient using a macro can be to simplify data!!

Anyone out there

Round of applause to you!! Thank you very much, the syntax you used is very simple too It is something I can definitely recreate now.

Thank you!

Originally Posted by JeteMc

Try the following:
For cell Y8: =AVERAGEIFS(Data!D15:T15,Data!D4:T4,">="&Y6,Data!D4:T4,"<="&AA6)
For cell AD9: =AVERAGEIFS(Data!D15:T15,Data!D4:T4,">="&AD6,Data!D4:T4,"<="&AI6,Data!D3:T3,AI7)
Note that AD9 will display a DIV/0 error because none of the drop downs for departments in the range Data!D3:T3 match any of the names listed in the dropdown for AI7. The data validation for one or the other will need to be changed in order for the second formula to display a value.
Let us know if you have any questions.

Originally Posted by JeteMc

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Hello JeteMC!

The code has worked absolutely smoothly but I have a question for you -

In the sheet in which I input new data I do this by inputting a new column - but that column causes the following code to change

=AVERAGEIFS(Data!D15:ZZ15,Data!D4:ZZ4,">="&Y6,Data!D4:ZZ4,"<="&AA6)

when I add a new column it changes to

=AVERAGEIFS(Data!E15:ZZ15,Data!E4:ZZ4,">="&Y6,Data!E4:ZZ4,"<="&AA6)

=AVERAGEIFS(Data!F15:ZZ15,Data!F4:ZZ4,">="&Y6,Data!F4:ZZ4,"<="&AA6)

and so on... how do I lock the code so that it stays on D15 and D4 .

Yours and anyone else's help would be greatly appreciated.
Thank you!

Originally Posted by JeteMc

Assuming that the new columns are added at the present column D (because column C is marked Template, Do not remove) amend the formula to read:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Ahhh yes perfect! I did the same thing for another code but did not think to try it here. Thank you very much again.