Auto filling dates based on previously entered dates and averaging numbers if dates equal

Hello all,

I am having a great deal of trouble with this. It is somewhat long and complicated.

I have two sheets I am dealing with. The first called 'Cycle Time' and the second called 'CT Hidden'.

'Cycle Time' sheet:

Column A are dates filled in by users
Column B is the type of service they are giving as a drop down list
Columns M-P are calculated times related to the service.

On 'CT Hidden'

I have a column for a date and a column for each measure. There is a separate chart for each type of service: eg.



Adult Well Child
Date Wait Time Time Behind Red Zone Cycle Date Wait Time Time Behind Red Zone Cylce



What I would like to do is display the first date listed on the 'Cycle Time' sheet in cell A3 of 'CT Hidden' depending on the clinic type they chose. For instance, if they chose CDC as the clinic type for a particular row in the 'Cycle Time' sheet, the date would populate in the date column of 'CT hidden'. Without duplicate dates appearing for each table. For instance, I wouldn't want 1-Dec-2012 appearing twice under adult.

For the remainder of the columns in 'CT Hidden' I would like each cell to look for rows in the 'Cycle Time' sheet and take an average of the values that match. For instance, if there are 3 rows in the 'Cycle Time' sheet that have the date 1-Dec-12 and "Adult" selected from the drop down list in the chart, then it would take those values and average them and that value would appear in the appropriate cell of 'CT Hidden'.

I've attached a copy of the spreadsheet for more clarity.

Improvement Measures Chart to play with.xls


I hope this makes sense.

Thanks,

Graham

Hello all,

I actually was able to solve this issue with use of some complex formulas such as:

Please Login or Register  to view this content.

Thanks everyone.