I have 10 worksheets labeled Jan-Dec. They all contain devices in column A, a time stamp in C and the value I want for the max in D.
I want a worksheet that displays the values. In this worksheet column A should be the device, B should be the max of all the spreadsheets for that device, C should be the date.
I have a large formula for the max's..
=MAX(MAX((Oct!$A$4:$A$3000=$A4)*(Oct!$D$4:$D$3000)),MAX((Nov!$A$4:$A$3000=$A4)*(Nov!$D$4:$D$3000)),MAX((Dec!$A$4:$A$3000=$A4)*(Dec!$D$4:$D$3000)),MAX((Jan!$A$4:$A$3000=$A4)*(Jan!$D$4:$D$3000)),MAX((Feb!$A$4:$A$3000=$A4)*(Feb!$D$4:$D$3000)),MAX((Mar!$A$4:$A$3000=$A4)*(Mar!$D$4:$D$3000)),MAX((Apr!$A$4:$A$3000=$A4)*(Apr!$D$4:$D$3000)),MAX((May!$A$4:$A$3000=$A4)*(May!$D$4:$D$3000)),MAX((June!$A$4:$A$3000=$A4)*(June!$D$4:$D$3000)),MAX((July!$A$4:$A$3000=$A4)*(July!$D$4:$D$3000)))
I am trying to use the index formula to find the date and I have having a lot of trouble with it.
I have tried to start it by only using two of the months but it will not work...
=INDEX((July!C4:C3000,Oct!C4:C3000),MATCH(MAX(MAX((July!$A$4:$A$3000=$A4)*(July!$D$4:$D$3000)),MAX((Oct!$A$4:$A$3000=$A4)*(Oct!$D$4:$D$3000))),(July!D4:D3000,Oct!D4:D3000),0))
Bookmarks