Using a date range and data from another sheet but using a from and to date cell in the new sheet to manipulate the data

[Detectiveclem]

I am using the formula below successfully on a workbook at (sheet 3) which gives me the number of contracts by the person named at E3 between the dates I enter a C8 & C10.


=SUMIFS(Test,Daterange,">="&$C4,Daterange,"<="&$C6,Manager,$E$3)

I now want to use the same base information on a separate summary summary sheet (Sheet 1) which draws all the information from sheet 3, but uses the start date at (C4) and end dates at (C6) from cells within sheet 1 (NOT sheet 3). Therefore the results would stay exactly the same the same dates are shown on both sheets, but would change when the dates are set differently between the two sheets.

The reason I need this, is that multiple users use individual sheets and therefore set the date ranges on their own sheets to suit their purposes, however on the summary sheet (sheet 1) I simply want to capture the information between dates without interfering with their settings.

I hope I have explained this sufficiently as any help will be greatly appreciated.

 

[JLGWhiz]

Would this not do it?

=SUMIFS(Test,Daterange,">="&Sheet1!$C4,Daterange,"<="&Sheet1!$C6,Manager,$E$3)

 

[Detectiveclem]

Hi JLGWhiz,

Thank you for trying to assist me, unfortunately this didn’t work for me.

Perhaps if I try to explain what I am trying to achieve.

I have 20 separate worksheets, all are individually named by users name.

Each sheet contains input data across a large number of columns. This data starts from row 16. I want to draw a summary from some of the data held on the individual’s sheet.

For example in Column D (from 16 down) dates are entered. In Column F (from 16 down) a named individual is entered and Column G (from 16 down) a status is entered, E.g. “Complete” “Awaits” “Cancelled” etc. And finally, at $E$3 on the sheet, is the team users name.

I want to pull this information onto a Management Summary Sheet, based on the criteria above, but want the date range to be controlled by using a start date in cell $C$4 and an end date in cell $C$6 on the Management Summary Sheet.

If you are able to help with the correct formula I can then adapt your formula to suit the differing criteria and for each user.

I hope my explanation assists you to assist me.

Cheers and thanks again