Looking for a value based on a date and value from 2 different cells.

[Tej92]

Hello everyone!
from the following table

Raised on	Action	Assigned date	Assigned to	completed
19/01/2023​	Extra equipment in the area	20/02/2023​	John Doe	06/02/2023​
29/01/2023​	No location for extra equipment	20/02/2023​	John Doe	open​

I'd like the cell to return the oldest action if under the completed column is still "open".
how can i do it?

Thanks in advance for the help!

 

[awoohaw]

Will this work?:

Book1
	A	B	C	D	E	F
1	Raised on	Action	Assigned date	Assigned to	completed
2	2023-01-19	Extra equipment in the area	2023-02-20	John Doe	2023-02-06	2023-02-20
3	2023-01-29	No location for extra equipment	2023-02-20	John Doe	open
4
Sheet3

Cell Formulas
Range	Formula
F2:F3	F2	=IF(E2="open","",MAX(A2,C2))

 

[awoohaw]

if you need the action text, then this may work:

Book1
	A	B	C	D	E	F	G
1	Raised on	Action	Assigned date	Assigned to	completed	Last Action Date	Action
2	2023-01-19	Extra equipment in the area	2023-02-20	John Doe	2023-02-06	2023-02-20	Extra equipment in the area
3	2023-01-29	No location for extra equipment	2023-02-20	John Doe	open
Sheet3

Cell Formulas
Range	Formula
F2:F3	F2	=IF(E2="open","",MAX(A2,C2))
G2:G3	G2	=IF(E2="open","",B2)

 

[Tej92]

Hi @awoohaw, thank you very much for the reply.

the table i posted is going to be quite long and there are several worksheets with data in the same format.
i have a general worksheet that has the titles of all the worksheets and next to it I need excel to write the action (column "B") by looking for the oldest (column "A") that has "open" (column "E").

how do you think i can achieve that?

 

[Tej92]

=VLOOKUP("open",CHOOSE({1,2},'area 1_Actions'!F:F,'area 1_Actions'!C:C),2,FALSE)

Managed to make it work using this formula.

 

[Peter_SSs]

I need excel to write the action (column "B") by looking for the oldest (column "A") that has "open" (column "E").

The formula in post #5 does not do that. It does not look at column A or column E at all. Perhaps you have changed your layout &/or requirement?

There may be a shorter &/or more efficient formula to get what you want if you would care to give a bit larger sample data and expected result(s), preferably with XL2BB so we can better see where the data is and easily copy it to our sheet for testing.

 

[Tej92]

Hi Peter, I Couldn't find anything online so wrote that formula as it return the first action that is open reading from the top. The actions are being submitted from another sheet by various users with a vba button. So the first action to be open from the top works for me.
At work we are not allowed to download extensions or software from the web so won't be able to send the XL2BB i can try to paste the table or upload a tesst file in the next couple of hours when i get the time.
Thanks again for the reply.

Tej.

 

[Tej92]

Hi @Peter_SSs, yes the table has changed a little.
desired result: "this is action 4"
the formula has to look through the column E for "open" and then the oldest date in column A and return the action from column B.

A	B	C	D	E
Raised on	Action	Assigned date	Assigned to	Completed
19/01/2023	this is action 1	10/02/2023	Tej	06/02/2023
23/01/2023	this is action 2	16/02/2023	Joe	open
28/01/2023	this is action 3	11/03/2023	john	10/02/2023
20/01/2023	this is action 4	18/03/2023	Mark	open

 

[Peter_SSs]

Still not sure that I fully understand, but what about this?

23 03 07.xlsm
	A	B	C	D	E	F	G
1	Raised on	Action	Assigned date	Assigned to	Completed
2	19/01/2023	this is action 1	10/02/2023	Tej	6/02/2023		this is action 4
3	23/01/2023	this is action 2	16/02/2023	Joe	open
4	28/01/2023	this is action 3	11/03/2023	john	10/02/2023
5	20/01/2023	this is action 4	18/03/2023	Mark	open
Tej92

Cell Formulas
Range	Formula
G2	G2	=INDEX(SORT(INDEX(A:A,MATCH("open",E:E,0)):B100,1),1,2)

 

[Tej92]

Good morning Peter, really appreciate the reply.
It works until I start adding another line, i added another line at the bottom with an older date in column A but it did not have open in column E, it still returned the new action line.
A little info on what I'm trying to achieve.
multiple users have another excel workbook, in that workbook they write the the action.
There is a submit button and once they click it, all the data regarding the action gets pasted in the above format in this workbook.
There is a summary sheets for all different users, in that summary sheet I'm trying to display the oldest action that is still open (that doesn't have a date in the column E, as if they complete the action there is another button that looks for the action and replaces "open" with the date they clicked the "complete" button).
Hope this makes it clearer.

thank you again for taking the time to follow this up, really appreciate it.