Latest Day (MAX across Multiple sheets)

I need to be able to find the highest value of the 'Last Day Audited' across about 13 sheets and return it to a master summary sheet. The trick here is that the names of the sheets have to correspond to the names of the auditors and may change over time, so I can't just use something like =MAX(Agent1!B14,Agent2!B14,Agent3!B14,Agent4!B14...) unless there's a way to dynamically adjust the criteria. I've tried using an array to define the names, but I haven't been able to get that to work quite right.

I feel like this should be a simple solution, but it's been haunting me for almost a month now. Hopefully someone here can shed some light on this dilemma!

Hello
One solution is to create two blank 'hidden' sheets named perhaps, Start and End. Make sure Start is your first sheet and End your last. Then formula:

=Max(Start:End!B14)

Whatever sheets you change in between Start and End should be calculated.

Just a note. The sheet with your formula must be outside the Start and End sheets or you'll get a circular reference.

Hope this helps.

Thanks for the quick replies! I had forgotten about the MAX(Start:End!B14) tack, and using INDIRECT or using Dionysos' VB idea might actually make it flexible enough... But it looks like in trying to simplify my initial post, I may have overlooked a detail that would cause problems with this approach.

The target value I'm looking for may not be in the same cell on each sheet. The idea behind this is that if one agent needs help with one of the individuals they're supposed to audit, then that individual can be reassigned to another agent.

It's a simple matter to get most of the information that needs to 'flow down' from the Master sheet to the individual Agent sheets and information to 'flow up' from Agent sheets to the Master sheet based solely on the input of the audited individual's name. I already have a formula in place that will sum the number of completed audits for a given individual across all sheets, but replacing that 'SUMIF' with 'MAX' or 'MAX(IF' didn't work:

Please Login or Register  to view this content.

It dynamically adjusts an array of the auditor's names' on an 'Overview' sheet. They're in a small table starting at Overview!J17 with a 'Total' row at the bottom, so it counts the number of blanks, subtracts that from the row number that includes 'Total', and you get the numerical range. (Presently Overview!J17:J27) It then matches the value in C2 (Name on the Master Sheet) to Column G (Name in the Auditor's sheet) in each sheet to add up the total in Column C (Number of audits completed) on each sheet.

Sorry if this seems unnecessarily complicated, but the flexibility is required to have this work smoothly for the entire team (and eventually the entire department).

Hello TesseractE,

Assume C2 to down you have names entered, so try this with CTRL+SHIFT+ENTER

=MAX(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(J$17:J$27)&"'!G1"),ROW(A$1:A$1000)-ROW(A$1),))=C2,N(OFFSET(INDIRECT("'"&TRANSPOSE(J$17:J$27)&"'!C1"),ROW(A$1:A$1000)-ROW(A$1),))))

Since you are on XL2007 you can avoid dynamic sheet lookup using IFERROR. adjust the range to until 'Total' cell

=MAX(IFERROR(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(J$17:J$30)&"'!G1"),ROW(A$1:A$100)-ROW(A$1),))=C2,N(OFFSET(INDIRECT("'"&TRANSPOSE(J$17:J$30)&"'!C1"),ROW(A$1:A$100)-ROW(A$1),))),0))

Better to limit only available sheet.

TesseractE, glad it works.

Let me try my level best to explain. I am not good in to explain something

Firstly consider Overview!J17:27 contains 13 sheets. Sheet1 to Sheet13

OFFSET part:

The first argument in OFFSET is Reference. which means a starting cell or the reference from which you want to base the offset. So here used INDIRECT("'"&TRANSPOSE(J$17:J$27)&"'!G1") which means Sheets entered in J17:J27!G1, so this will become.

{"'Sheet1'!G1";"'Sheet2'!G1";"'Sheet3'!G1";"'Sheet4'!G1";"'Sheet5'!G1";"'Sheet6'!G1";"'Sheet7'!G1";"'Sheet8'!G1";"'Sheet9'!G1";"'Sheet10'!G1";"'Sheet11'!G1";"'Sheet12'!G1";"'Sheet13'!G1"}

currently the sheets are in row vise TRANSPOSE will make them in column vise,

{"'Sheet1'!G1","'Sheet2'!G1","'Sheet3'!G1","'Sheet4'!G1","'Sheet5'!G1","'Sheet6'!G1","'Sheet7'!G1","'Sheet8'!G1","'Sheet9'!G1","'Sheet10'!G1","'Sheet11'!G1","'Sheet12'!G1","'Sheet13'!G1"}

So this way OFFSET will make 13 columns in calculation. 1 for Sheet1, 2nd for Sheet2, 3rd fro Sheet3 etc...

ROW(A$1:A$1000)-ROW(A$1) will look in next 1000 rows. G1, then G2, then G3, then G4 etc....G1000

The calculation will become like this,

Sheet1!G1;Sheet1!G2;Sheet1!G3...Sheet1!G1000,Sheet2!G1;Sheet2!G2;Sheet2!G3....Sheet2!G1000,Sheet13!G1;Sheet13!G2;Sheet13!G3...Sheet13!G1000

T() function will dereference it's actual values. ONLY text will display, rest of them will be blank, numbers & logical values.

So,

T(OFFSET(INDIRECT("'"&TRANSPOSE(J$17:J$27)&"'!G1"),ROW(A$1:A$1000)-ROW(A$1),))=C2

This will check each cells in G1:G1000=C2 or not. If G1:G1000=C2 will give TRUE otherwise FALSE.

Same method in the 2nd part.

N(OFFSET(INDIRECT("'"&TRANSPOSE(J$17:J$27)&"'!C1"),ROW(A$1:A$1000)-ROW(A$1),))

Start cell is C1 then to C1000. N() function will dereference it's actual values. ONLY numbers will display all the text will be blank.

IF() function will take only value where condition is TRUE which is G1:G1000=C2. then return the MAX value in the array.

Here is some about Array Formula

Hope this helps you.