I have a cell that is supposed to scan a range for a name, and then return the max (most recent) date from another range. Essentially I'm making a last date contacted cell for case managers. I had it all worked out but because I was using direct cell references, everything got messed up when I sorted the rows.
This is the closest I could get to an INDIRECT array formula:
=MAX(IF(Encounters!A$3:A$1000 = INDIRECT("Caselist"&"!"&"B"&ROW()), Encounters!C$3:C$1000))
A little help with syntax would be much appreciated. If I type
=INDIRECT("Caselist"&"!"&"B"&ROW())
into another cell it returns the value I'm looking for.
Avoid the volatile INDIRECT whenever possible. In most cases, the non-volatile INDEX can be used.
=MAX(IF(Encounters!A$3:A$1000 = INDEX(Caselist!B:B, ROW()), Encounters!C$3:C$1000))
This is an array formula and requires CSE.