A
Alison
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!
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!
1000=B19)*(C1:C1000<A19),C1:C1000))