How to make =MAX(IF incorporate "less than"?
 

I am entering project information into a template, which is then saved to a
database. In this template, I use project numbers, and as I open new
projects those numbers get higher.

The following formula references a cell in the template, B19, which is a
description, and then looks in the database for the highest matching project
number that also has that description in its record. Column D is the
description and Column C is the project number. It gives me back a previous
project number that I can use for reference information (the most recent
similar project).

=MAX(IF('[Database.xls]Sheet1'!$D1:$D1000=B19,'[Database.xls]Sheet1'!$C1:$C1
000))

The MAX part gives me the highest match and the IF part makes it look only
for projects that match the description I tell it.

I thought this was the answer to my problem, and it works as I go along in
ascending order, but if I then later open up an old job from the template, it
automatically updates it with new information that isn't valid for the time
that this job is opened. Instead, I need for it to keep the information that
was accurate only at the time I created the project.

This means I need to incorporate into the formula a way for it to tell me
what the MAX is BUT less than than the project's own project number, which is
in cell B4.

CAN THIS BE DONE? Thanks!

Hi!

Add the appropriate file and sheet names.

Array entered:

A19 = project number

=MAX(IF((D1:D1000=B19)*(C1:C1000<A19),C1:C1000))

Biff

"Alison" wrote in message
...
I am entering project information into a template, which is then saved to a
database. In this template, I use project numbers, and as I open new
projects those numbers get higher.

The following formula references a cell in the template, B19, which is a
description, and then looks in the database for the highest matching
project
number that also has that description in its record. Column D is the
description and Column C is the project number. It gives me back a
previous
project number that I can use for reference information (the most recent
similar project).

=MAX(IF('[Database.xls]Sheet1'!$D1:$D1000=B19,'[Database.xls]Sheet1'!$C1:$C1
000))

The MAX part gives me the highest match and the IF part makes it look only
for projects that match the description I tell it.

I thought this was the answer to my problem, and it works as I go along in
ascending order, but if I then later open up an old job from the template,
it
automatically updates it with new information that isn't valid for the
time
that this job is opened. Instead, I need for it to keep the information
that
was accurate only at the time I created the project.

This means I need to incorporate into the formula a way for it to tell me
what the MAX is BUT less than than the project's own project number, which
is
in cell B4.

CAN THIS BE DONE? Thanks!

You are brilliant! Thank you!

"Biff" wrote:

Hi!

Add the appropriate file and sheet names.

Array entered:

A19 = project number

=MAX(IF((D1:D1000=B19)*(C1:C1000<A19),C1:C1000))

Biff

"Alison" wrote in message
...
I am entering project information into a template, which is then saved to a
database. In this template, I use project numbers, and as I open new
projects those numbers get higher.

The following formula references a cell in the template, B19, which is a
description, and then looks in the database for the highest matching
project
number that also has that description in its record. Column D is the
description and Column C is the project number. It gives me back a
previous
project number that I can use for reference information (the most recent
similar project).

=MAX(IF('[Database.xls]Sheet1'!$D1:$D1000=B19,'[Database.xls]Sheet1'!$C1:$C1
000))

The MAX part gives me the highest match and the IF part makes it look only
for projects that match the description I tell it.

I thought this was the answer to my problem, and it works as I go along in
ascending order, but if I then later open up an old job from the template,
it
automatically updates it with new information that isn't valid for the
time
that this job is opened. Instead, I need for it to keep the information
that
was accurate only at the time I created the project.

This means I need to incorporate into the formula a way for it to tell me
what the MAX is BUT less than than the project's own project number, which
is
in cell B4.

CAN THIS BE DONE? Thanks!

You're welcome!

Biff

"Alison" wrote in message
...
You are brilliant! Thank you!

"Biff" wrote:

Hi!

Add the appropriate file and sheet names.

Array entered:

A19 = project number

=MAX(IF((D1:D1000=B19)*(C1:C1000<A19),C1:C1000))

Biff

"Alison" wrote in message
...
I am entering project information into a template, which is then saved
to a
database. In this template, I use project numbers, and as I open new
projects those numbers get higher.

The following formula references a cell in the template, B19, which is
a
description, and then looks in the database for the highest matching
project
number that also has that description in its record. Column D is the
description and Column C is the project number. It gives me back a
previous
project number that I can use for reference information (the most
recent
similar project).

=MAX(IF('[Database.xls]Sheet1'!$D1:$D1000=B19,'[Database.xls]Sheet1'!$C1:$C1
000))

The MAX part gives me the highest match and the IF part makes it look
only
for projects that match the description I tell it.

I thought this was the answer to my problem, and it works as I go along
in
ascending order, but if I then later open up an old job from the
template,
it
automatically updates it with new information that isn't valid for the
time
that this job is opened. Instead, I need for it to keep the
information
that
was accurate only at the time I created the project.

This means I need to incorporate into the formula a way for it to tell
me
what the MAX is BUT less than than the project's own project number,
which
is
in cell B4.

CAN THIS BE DONE? Thanks!