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

[Guest]

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!

 

[Biff]

Hi!

Add the appropriate file and sheet names.

Array entered:

A19 = project number

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

Biff

 

[Guest]

You are brilliant! Thank you!

Hi!

Add the appropriate file and sheet names.

Array entered:

A19 = project number

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

Biff

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!

 

[Biff]

You're welcome!

Biff

You are brilliant! Thank you!

Hi!

Add the appropriate file and sheet names.

Array entered:

A19 = project number

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

Biff

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!