You're welcome. Thanks for the feedback!

Biff

"Alison" wrote in message
...
Thanks so much, Biff!


"Biff" wrote:

Hi!

=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))

A couple of things:

This is an array formula. An array formula cannot reference entire
columns,
D:D, C:C

Reduce the range reference size:

=MAX(IF('[Database.xls]Sheet1'!$D2:$D65536=B5,'[Database.xls]Sheet1'!$C2:$C65536))

Do you really need to use the entire column as a reference?

Also, since this is an array formula you MUST use the key combo of
CTRL,SHIFT,ENTER instead of just using ENTER.

Biff

"Alison" wrote in message
...
I also just tried
=(IF(('[Database.xls]Sheet1'!$D:$D)=B5,(MAX('[Database.xls]Sheet1'!$C:$C)),"N/A"))

which didn't work... returned the "N/A" which is my value if false.
There
should have been an actual job number returned from Column C.

"Alison" wrote:

I really thought your MAX formula would work, but for some reason it
didnt.
In my scenario, the "Calif Insert" is a changing criteria that I need
to
be
able to input in a cell. Then the formula needs to look at that cell
and
then go to the database to find all the records that also contain that
same
description. Then among all the ones it finds that that description
in
one
field, return a number to me from another field... the highest number
from
the matches.
So what I did was on my template (not my database, create a formula
that
refers to the database and to a cell on the template:
=MAX(IF('[Database.xls]Sheet1'!$D:$D=B5,'[Database.xls]Sheet1'!$C:$C))
Column D is the database column with the description like "Calif
Insert"
or
whatever alphabetic description I need to match. In that same column
there
will be other descriptions like "Harris Print Ad" or "First
Newsletter"
etc.
B5 is the cell on the template that I will input that same
description.
Column C has the project numbers like 5068 or 5037 etc., and I need
the
largest one of those records that matches the description in column D.

Unless there's a problem with referencing a cell like B5, I'm not sure
why
it didn't work.
It returned a value of 0. It should have returned an actual job
number
from
my test entries.
"Biff" wrote:

Hi!

(Instead, I need to look at all the ones that match and return a
field
from
the largest alphnumberic match).

Can you post some examples of these "alpha-numerics" and how do you
determine one is larger than another?

Do you mean something like:

2001-10-B
2000-75-Z

So the answer would be the job number which might look something
like
"5002"

If 5002 is a real number:

=MAX(IF(A1:A100="Calif Insert", B1:B100))

Entered as an array using the key combo of CTRL,SHIFT,ENTER.

Biff

"Alison" wrote in message
...
I have a template that sends info to a database. In that template,
I
need
to
put in a function that will use alphabetic information in a cell
as
my
criteria. It then should look in the database to find records
that
match
that criteria and then send back to me the largest match from a
field
of
the
record among the records that match the criteria.
I have tried several functions, the best of which were VLOOKUP and
DMAX.
Neither was what I needed. VLOOKUP gives field from the first
record
of
the
list that matches the criteria. (Instead, I need to look at all
the
ones
that match and return a field from the largest alphnumberic
match).
DMAX
for
some reason doesn't work when the database is closed and it
doesn't
let me
specify criteria for a field.

Here's an example: the template is used to enter project
information
for
several clients. Each of those clients may do similar projects.
Of
those
projects and within each client, several of the projects may be
similar.
Let's say I have two projects and need to find the most recent
that
was
similar to another project. Projects have project numbers that go
higher
as
they become more recent. In the template, I can have the
information
"Calif
Insert" which represents the client name and project type, and
want
the
function to go to the database to find all the records that have
"Calif
Insert" in a specified column. Then find me from another
specified
field
the
largest value in that field that matches "Client Insert" in the
other
field.
So the answer would be the job number which might look something
like
"5002"
which represents the most recent job (highest numbered job) that
is
also
described as "Calif Insert"
As I said, VLOOKUP was very close, but gave me the FIRST in the
list
that
matched the criteria "CLIENT NAME PROJECT" but not the highest. I
guess
VLOOKUP doesn't have the ability to go past the first one it sees.
Sorting
the database is not an option.
I am desparate. Any help would be appreciated.
Thanks.