Dmax formula

[Angie G.]

I have a spreadsheet with data from a1..j266 listing all of our injuries by
Rig no. and dates, etc. I want to pull the latest injury date per rig.

I have a define name of "database" (which covers a1..j266)
Defiine name of "criteria" (which covers L1..M2)
Column 6 is "Date of Accident"

On L1 - typed Rig No.
On M1 - typed Last recordable date
On M4 - typed =dmax(database,6,criteria)

L5-L45 - typed rig nos.
*****
When I was using Excel 97, I would then go to Data, Table, in the Column
Input Cell box, select L2, OK and formula would work.

We have updated to Excel 2007, I get all the way to the ******; then can't
find the same options of Data, Table, Column Input Cell Box for the formula
to work.

Can someone please tell me what I need to do?

Thanks,

Angie

L

 

[Ashish Mathur]

Hi,

in Excel 2007, Data tables are located under Data > Data Tools > What if
analysis > Data Table

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

How about a much easier to understand and implement alternative?

I assume the latest injury date means the *most recent date*.

A2:An = rig numbers
B2:Bn = dates of injury

D2n = unique rig numbers

Enter this array formua** in E2 to return the most recent date for each rig:

=MAX(IF(A$2:A$n=D2,B$2:B$n))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down as needed and format as Date.