Don't get database functions

**ahartman** · 02-26-2010, 03:12 PM

Hi all,

I've got a List of project hours booked on Sheet1, and a summary List by project on Sheet2. For each project, I want to have a cell on the summary sheet that gives the latest date with hours for that job. So, if Sheet1 has ProjectA with some dates and hours, ProjectB w/ dates and hours, etc, and the summary Sheet2 has one row for ProjectA, one for ProjectB, etc, how do I use the dmax() function to get the latest date for a given project? Or, is that even the right way to do it?

Thanks in advance if anyone can clear this up for me!

-Adam

**DonkeyOte** · 02-26-2010, 03:39 PM

I don't use the Dx functions that much myself - I'm sure you can use them but if you have a lot of projects the layout might not be great (I think [offhand] you would need field header above each Project name)

You could potentially use LOOKUP depending on whether the records are sorted ?

If the Sheet1 transactions are listed such that the last recorded instance of a given project is the Max date then if we assume:

Sheet1!A1:A100 contains Projects, B contains date & C hours
Sheet2!A2 contains project of interest

then

Sheet2!B2 - "last date"
=LOOKUP(2,1/(Sheet1!$A$1:$A$100=$A2),Sheet1!B$1:B$100)

Sheet2!C2 - "last hours"
=LOOKUP(2,1/(Sheet1!$A$1:$A$100=$A2),Sheet1!C$1:C$100)
(ie as above but altered result vector - B formula can be copied across)

If the above assumption regards data on Sheet1 does not hold true then other alternatives would generally be Array based and even less efficient (assuming you opt against DMAX).

(edit: you might also get away with a Pivot Table but that may / may not be viable for you - unknown)

**DP978** · 02-26-2010, 04:05 PM

The above poster is correct, DB functions are very annoying in excel. They require you to set up a conditional array of cells and list the Column Headers of every condition as well as a proper boolean logic function.

Most figures can be derived elsewise. If you need more help post examples and we will work out some useful formulas.

(the big reason the D* formulas don't do much is because all conditions can be written just as easily in an IF() statement. With a little creativity you never need to use anythign else when thinking conditionally.)