Find next instance of name/number in column and return date if matches criteria

[jaxo]

Hi, I need a formula to be entered into the Date Out column of example sheet, correct date is posted to the right of table as example. Formula should return a date next to "Opening Stock" or "Move +". It should read the next instance of Paddock number and if the process is Opening Stock" or "Move +, record the date from that row in the Date Out column next to the above "Opening Stock" or "Move +" Would appreciate solutions.

LSR Forrester 2023b.xlsx
	E
14
Input

 

[jaxo]

LSR Forrester 2023b.xlsx
	A	B	C	D	E	F	G
2
3	Date	Process	Paddock/s	Herd	Date Out		Date Out
4	1/11/2023	Opening Stock	1	Red Cows			7/11/2023
5	17/10/2023	Opening Stock	6	Bont Cows			3/11/2023
6	1/11/2023	Opening Stock	4	Grey Cows			7/11/2023
7	14/10/2023	Opening Stock	2	White Cows			3/11/2023
8	1/11/2023	Opening Stock	7	Weaner Heifers & Bulls			3/11/2023
9	1/11/2023	Opening Stock	9	Sale Herd			6/11/2023
10	1/11/2023	Opening Stock	Not Used	Bulls
11	7/11/2023	Move -	1	Red Cows
12	7/11/2023	Move +	3	Red Cows			9/11/2023
13	3/11/2023	Move -	6	Bont Cows
14	3/11/2023	Move +	8	Bont Cows			6/11/2023
15	6/11/2023	Move -	8	Bont Cows
16	6/11/2023	Move +	9	Bont Cows			11/11/2023
17	7/11/2023	Move -	4	Grey Cows
18	7/11/2023	Move +	East Old Lands (L3)	Grey Cows
19	3/11/2023	Move -	2	White Cows
20	3/11/2023	Move +	10	White Cows			6/11/2023
21	6/11/2023	Move -	10	White Cows
22	6/11/2023	Move +	11	White Cows			9/11/2023
23	3/11/2023	Move -	7	Weaner Heifers & Bulls
24	3/11/2023	Move +	Dam	Weaner Heifers			14/11/2023
25	3/11/2023	Move -	7	Weaner Heifers & Bulls
26	3/11/2023	Move +	15 & L2	Weaner Bulls
27	9/11/2023	Move -	3	Red Cows
28	9/11/2023	Move +	2	Red Cows			10/11/2023
29	9/11/2023	Move -	11	White Cows
30	9/11/2023	Move +	12	White Cows			14/11/2023
31	10/11/2023	Move -	2	Red Cows
32	10/11/2023	Move +	1	Red Cows			11/11/2023
33	10/11/2023	Died/Losses -	Dam	Weaner Heifers
34	11/11/2023	Move -	1	Red Cows
35	11/11/2023	Move +	5	Red Cows			14/11/2023
36	11/11/2023	Move -	9	Bont Cows
37	11/11/2023	Move +	10	Bont Cows			14/11/2023
38	14/11/2023	Move -	10	Bont Cows
39	14/11/2023	Move +	11	Bont Cows			17/11/2023
40	14/11/2023	Move -	12	White Cows
41	14/11/2023	Move +	13	White Cows			17/11/2023
42	14/11/2023	Move -	5	Red Cows
43	14/11/2023	Move +	6	Red Cows			17/11/2023
44	14/11/2023	Move -	Dam	Weaner Heifers
45	14/11/2023	Move +	7	Weaner Heifers
46	17/11/2023	Move -	11	Bont Cows
47	17/11/2023	Move +	12	Bont Cows			20/11/2023
48	17/11/2023	Move -	13	White Cows
49	17/11/2023	Move +	9	White Cows			20/11/2023
50	17/11/2023	Move -	6	Red Cows
51	17/11/2023	Move +	3	Red Cows			19/11/2023
52	19/11/2023	Move -	3	Red Cows
53	19/11/2023	Move +	2	Red Cows			20/11/2023
54	20/11/2023	Move -	2	Red Cows
55	20/11/2023	Move +	1	Red Cows
56	20/11/2023	Move -	12	Bont Cows
57	20/11/2023	Move +	8	Bont Cows
58	20/11/2023	Move -	9	White Cows
59	20/11/2023	Move +	11	White Cows
60	Total
Input

Cells with Data Validation
Cell	Allow	Criteria
B4:B59	List	=Process
C4:C59	List	=Paddock
D4:D59	List	=Lists!$E$4:$E$12

 

[Peter_SSs]

Formula should return a date next to "Opening Stock" or "Move +". It should read the next instance of Paddock number and if the process is Opening Stock" or "Move +, record the date from that row in the Date Out column next to the above "Opening Stock" or "Move +"

Welcome to the MrExcel board!

1. Can you explain how you get 6/11/2023 for row 9?
2. Can you explain why row 10 is empty in the result column?

 

[jaxo]

Thank you Peter

1. Row 9 Should be empty, sorry. Not a good example
2. Row 10 is empty as cattle have not yet been moved out of the "Not Used" Paddock

 

[Peter_SSs]

Thanks. See if this is it.

23 11 22.xlsm
	A	B	C	D	E	F	G
1
2
3	Date	Process	Paddock/s	Herd	Date Out		Date Out
4	1/11/2023	Opening Stock	1	Red Cows	7/11/2023		7/11/2023
5	17/10/2023	Opening Stock	6	Bont Cows	3/11/2023		3/11/2023
6	1/11/2023	Opening Stock	4	Grey Cows	7/11/2023		7/11/2023
7	14/10/2023	Opening Stock	2	White Cows	3/11/2023		3/11/2023
8	1/11/2023	Opening Stock	7	Weaner Heifers & Bulls	3/11/2023		3/11/2023
9	1/11/2023	Opening Stock	9	Sale Herd			6/11/2023
10	1/11/2023	Opening Stock	Not Used	Bulls
11	7/11/2023	Move -	1	Red Cows
12	7/11/2023	Move +	3	Red Cows	9/11/2023		9/11/2023
13	3/11/2023	Move -	6	Bont Cows
14	3/11/2023	Move +	8	Bont Cows	6/11/2023		6/11/2023
15	6/11/2023	Move -	8	Bont Cows
16	6/11/2023	Move +	9	Bont Cows	11/11/2023		11/11/2023
17	7/11/2023	Move -	4	Grey Cows
18	7/11/2023	Move +	East Old Lands (L3)	Grey Cows
19	3/11/2023	Move -	2	White Cows
20	3/11/2023	Move +	10	White Cows	6/11/2023		6/11/2023
21	6/11/2023	Move -	10	White Cows
22	6/11/2023	Move +	11	White Cows	9/11/2023		9/11/2023
23	3/11/2023	Move -	7	Weaner Heifers & Bulls
24	3/11/2023	Move +	Dam	Weaner Heifers	10/11/2023		14/11/2023
25	3/11/2023	Move -	7	Weaner Heifers & Bulls
26	3/11/2023	Move +	15 & L2	Weaner Bulls
27	9/11/2023	Move -	3	Red Cows
28	9/11/2023	Move +	2	Red Cows	10/11/2023		10/11/2023
29	9/11/2023	Move -	11	White Cows
30	9/11/2023	Move +	12	White Cows	14/11/2023		14/11/2023
31	10/11/2023	Move -	2	Red Cows
32	10/11/2023	Move +	1	Red Cows	11/11/2023		11/11/2023
33	10/11/2023	Died/Losses -	Dam	Weaner Heifers
34	11/11/2023	Move -	1	Red Cows
35	11/11/2023	Move +	5	Red Cows	14/11/2023		14/11/2023
36	11/11/2023	Move -	9	Bont Cows
37	11/11/2023	Move +	10	Bont Cows	14/11/2023		14/11/2023
38	14/11/2023	Move -	10	Bont Cows
39	14/11/2023	Move +	11	Bont Cows	17/11/2023		17/11/2023
40	14/11/2023	Move -	12	White Cows
41	14/11/2023	Move +	13	White Cows	17/11/2023		17/11/2023
42	14/11/2023	Move -	5	Red Cows
43	14/11/2023	Move +	6	Red Cows	17/11/2023		17/11/2023
44	14/11/2023	Move -	Dam	Weaner Heifers
45	14/11/2023	Move +	7	Weaner Heifers
46	17/11/2023	Move -	11	Bont Cows
47	17/11/2023	Move +	12	Bont Cows	20/11/2023		20/11/2023
48	17/11/2023	Move -	13	White Cows
49	17/11/2023	Move +	9	White Cows	20/11/2023		20/11/2023
50	17/11/2023	Move -	6	Red Cows
51	17/11/2023	Move +	3	Red Cows	19/11/2023		19/11/2023
52	19/11/2023	Move -	3	Red Cows
53	19/11/2023	Move +	2	Red Cows	20/11/2023		20/11/2023
54	20/11/2023	Move -	2	Red Cows
55	20/11/2023	Move +	1	Red Cows
56	20/11/2023	Move -	12	Bont Cows
57	20/11/2023	Move +	8	Bont Cows
58	20/11/2023	Move -	9	White Cows
59	20/11/2023	Move +	11	White Cows
60	Total
Stock

Cell Formulas
Range	Formula
E4:E59	E4	=IF(OR(B4={"Opening Stock","Move +"}),IFERROR(INDEX(FILTER(A5:A$60,D5:D$60=D4),1),""),"")

 

[jaxo]

Thank you Peter, that looks impressive, however I need to copy back to Excel to check. I am new to this I press the little copy button but when I go to paste in Excel I can only paste as text?

 

[Peter_SSs]

.. but when I go to paste in Excel I can only paste as text?

Click the main Paste button itself, not the drop-down at the bottom of the Paste button.

 

[jaxo]

Thank you Peter, I could only paste values but entered your formula directly into my sheet. It worked but when I extended my table to enter some more dates and paddocks, it mentioned some inconsistent column formula. I had some problems with this, is there a way to make it extend automatically when I extend my table?

 

[Peter_SSs]

Just change the two 60 values in the formula to 1000 or any number that will definitely greater than the number of rows that you will ever have and copy the formula down as far as you might ever need.

 

[jaxo]

Just change the two 60 values in the formula to 1000 or any number that will definitely greater than the number of rows that you will ever have and copy the formula down as far as you might ever need.

Thank you very much for your help. If I have follow up questions e.g. I might need to calculate days out of a paddock. If I don't win can I ask you again on this thread? Really appreciate your help.