Formula to create date by looking for highest date based on criteria and adding 1.

[DerekWooley]

Hi, I would like to know if there is a formula that could to create a date by looking for the highest date with the same criteria and then adding 1 day to it to make it greater. For example, in the below table I want the last row in Col E to look at item 932 and see that 11/3/2019 is the highest date, then make the last cell in Col E 11/4/2019. Also, for 2972, I want the last row for that item to look at 2/23/2019, and make it 2/24/2019.

[TABLE="width: 664"]
<tbody>[TR]
[TD]Col A
[/TD]
[TD]Col B
[/TD]
[TD]Col C
[/TD]
[TD]Col D
[/TD]
[TD]Col E
[/TD]
[TD][/TD]
[TD]Col F
[/TD]
[/TR]
[TR]
[TD]Item No.
[/TD]
[TD]CUR STK
[/TD]
[TD]Projected Stock
[/TD]
[TD]Machine
[/TD]
[TD]Start Date
[/TD]
[TD][/TD]
[TD]Mach Run Qty
[/TD]
[/TR]
[TR]
[TD]2972
[/TD]
[TD]2500
[/TD]
[TD]2500
[/TD]
[TD]Mach 1
[/TD]
[TD]6/29/2018
[/TD]
[TD][/TD]
[TD]11,000
[/TD]
[/TR]
[TR]
[TD]2972
[/TD]
[TD]2500
[/TD]
[TD]13500
[/TD]
[TD]Mach 1
[/TD]
[TD]2/23/2019
[/TD]
[TD][/TD]
[TD]17,000
[/TD]
[/TR]
[TR]
[TD]2972
[/TD]
[TD]2500
[/TD]
[TD]13500
[/TD]
[TD]Mach 2
[/TD]
[TD]1/11/2019
[/TD]
[TD][/TD]
[TD]7,000
[/TD]
[/TR]
[TR]
[TD]2972
[/TD]
[TD]2500
[/TD]
[TD]37500
[/TD]
[TD]Mach 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]8,000
[/TD]
[/TR]
[TR]
[TD]932
[/TD]
[TD]4500
[/TD]
[TD]4500
[/TD]
[TD]Mach 1
[/TD]
[TD]6/5/2018
[/TD]
[TD][/TD]
[TD]30,000
[/TD]
[/TR]
[TR]
[TD]932
[/TD]
[TD]4500
[/TD]
[TD]34500
[/TD]
[TD]Mach 1
[/TD]
[TD]8/8/2019
[/TD]
[TD][/TD]
[TD]50,000
[/TD]
[/TR]
[TR]
[TD]932
[/TD]
[TD]4500
[/TD]
[TD]84500
[/TD]
[TD]Mach 2
[/TD]
[TD]11/3/2019
[/TD]
[TD][/TD]
[TD]27,000
[/TD]
[/TR]
[TR]
[TD]932
[/TD]
[TD]4500
[/TD]
[TD]111500
[/TD]
[TD]Mach 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD]27,000
[/TD]
[/TR]
</tbody>[/TABLE]

 

[DerekWooley]

I also wanted to add that I want to be able to drag the formula down since I have like 600 of these dates to fill out but I need to avoid circular references since it looks at the same column. Would I use a helper column of some sort?

 

[Peter_SSs]

Note that my dates are in d/m/y format, but see if this, copied down, is what you are after.

Excel Workbook
	A	B	C	D	E	F	G
1	Item No.	CUR STK	Projected Stock	Machine	Start Date	Mach Run Qty
2	2972	2500	2500	Mach 1	29/06/2018	11,000
3	2972	2500	13500	Mach 1	23/02/2019	17,000
4	2972	2500	13500	Mach 2	11/01/2019	7,000
5	2972	2500	37500	Mach 2		8,000	24/02/2019
6	932	4500	4500	Mach 1	5/06/2018	30,000
7	932	4500	34500	Mach 1	8/08/2019	50,000
8	932	4500	84500	Mach 2	3/11/2019	27,000
9	932	4500	111500	Mach 2		27,000	4/11/2019
Max date +1

 

[DerekWooley]

Thank you very much for your help.

 

[Peter_SSs]

Thank you very much for your help.

You are welcome.