Auto add last entered data in same row

[Detectiveclem]

Hi can anyone help with a formula which will allow the last date entered in a row to be captured in the last date entered cell (E.G. Cell A2) And the last Item entered to appear in the last item entered cell (EG Cell B2).

Please see the attached sample with the data shown in green for illustration purposes to show the data I want captured.

So for example at present A4 shows 10/01/2021 and B4 shows Application but if further dates or items are entered in G4 / H4 then A4 and B4 change to show the latest information.

I hope I have explained this sufficiently.

Your genius would be greatly appreciated.

 

[Detectiveclem]

 

[Detectiveclem]

Book1.xlsx
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1
2
3
4
5	LAST UPDATED	Last Item sent	Date sent	Method	Item	Rec'd back date	Complete	Date sent	Method	Item	Rec'd back date	Complete	Date sent	Method	Item	Rec'd back date	Complete	Date sent	Method	Item	Rec'd back date	Complete	OPT OUT Reason
6	3/21/21	FORM C	1/1/21	Post	FORM A	2/2/21	NO	1/2/21	Email	Message 1	1/21/21	NO	2/3/21	email	FORM B	2/10/21	no	3/21/21	EMAIL	FORM C
7	1/1/21	FORM A	1/1/21	Post	FORM A
8	2/5/21	FORM B	1/2/21	Post	FORM A	2/3/21	NO	2/5/21	Post	FORM B
9	2/3/21	Message 2	1/3/21	Post	FORM A	2/2/21	NO	1/2/21	Email	Message 1	1/21/21	NO	2/3/21	email	Message 2
10	NO ENTRIES	NO DATA
11	3/2/21	0	1/5/21	Post	FORM A	3/2/21	YES
12	1/6/21	FORM A	1/6/21	Post	FORM A
13	NO ENTRIES	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use	Do Not Use
14
15
Sheet1

Cell Formulas
Range	Formula
N6:N12	N6	=IFERROR(INDEX(Q6:AF6,MATCH(M6,O6:AD6,)),"NO DATA")
M6:M13	M6	=IFERROR(AGGREGATE(14,6,O6:AD6,1),"NO ENTRIES")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Y13	Cell Value	contains "Postal"	text	NO
Y13	Cell Value	contains "Awaiting"	text	NO
Y13	Cell Value	contains "OPT OUT"	text	NO
Y13	Cell Value	contains "Do Not Use"	text	NO
Y13	Cell Value	contains "Partially"	text	NO
Y13	Cell Value	contains "No"	text	NO
Y13	Cell Value	contains "Yes"	text	NO
Y13	Cell Value	contains "Invalid phone number"	text	NO
Z13:AH13	Cell Value	contains "Postal"	text	NO
Z13:AH13	Cell Value	contains "Awaiting"	text	NO
Z13:AH13	Cell Value	contains "OPT OUT"	text	NO
Z13:AH13	Cell Value	contains "Do Not Use"	text	NO
Z13:AH13	Cell Value	contains "Partially"	text	NO
Z13:AH13	Cell Value	contains "No"	text	NO
Z13:AH13	Cell Value	contains "Yes"	text	NO
Z13:AH13,AG14:AG15	Cell Value	contains "Invalid phone number"	text	NO
AG14:AG15	Cell Value	contains "Postal"	text	NO
AG14:AG15	Cell Value	contains "Awaiting"	text	NO
AG14:AG15	Cell Value	contains "OPT OUT"	text	NO
AG14:AG15	Cell Value	contains "Do Not Use"	text	NO
AG14:AG15	Cell Value	contains "Partially"	text	NO
AG14:AG15	Cell Value	contains "No"	text	NO
AG14:AG15	Cell Value	contains "Yes"	text	NO
M5	Cell Value	contains "~?"	text	NO
M5	Cell Value	contains "NO"	text	NO
M5	Cell Value	contains "YES"	text	NO

 

[billszysz]

for M6 (should works in 2016)

=IF(MAX(IF($O$5:$AH$5="Date sent",O6:AH6,"")),MAX(IF($O$5:$AH$5="Date sent",O6:AH6,"")),"")

 

[billszysz]

ok check these below
for M6 (Control + Shift + Enter in Excel 2016)

=IF(MAX(IF($O$5:$AH$5="Date sent",O6:AH6,"")),MAX(IF($O$5:$AH$5="Date sent",O6:AH6,"")),"NO ENTRIES")

for N6

=IF(M6="NO ENTRIES", IF(O6="Do Not Use", "Do Not Use","NO DATA"),(INDEX(O6:AH6,MATCH(M6,O6:AH6,0)+2)))

 

[Fluff]

Change to formula in col M to

=IFERROR(1/(1/AGGREGATE(14,6,O6:AD6/($O$5:$AD$5="Date Sent"),1)),"NO ENTRIES")

and in col N

=IF(M6="No entries","NO DATA",INDEX(Q6:AF6,MATCH(M6,O6:AD6,)))

 

[Detectiveclem]

for M6 (should works in 2016)

=IF(MAX(IF($O$5:$AH$5="Date sent",O6:AH6,"")),MAX(IF($O$5:$AH$5="Date sent",O6:AH6,"")),"")

Hi billszysz, still unable to get your formulas to work. But I think FLUFF has solved it. Thank you for trying to help me.

 

[Detectiveclem]

Change to formula in col M to

=IFERROR(1/(1/AGGREGATE(14,6,O6:AD6/($O$5:$AD$5="Date Sent"),1)),"NO ENTRIES")

and in col N

=IF(M6="No entries","NO DATA",INDEX(Q6:AF6,MATCH(M6,O6:AD6,)))

Hi Fluff, thank you both formulas are now working brilliantly. I really appreciate your help and perseverance. Have a good day.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Detectiveclem]

Change to formula in col M to

=IFERROR(1/(1/AGGREGATE(14,6,O6:AD6/($O$5:$AD$5="Date Sent"),1)),"NO ENTRIES")

and in col N

=IF(M6="No entries","NO DATA",INDEX(Q6:AF6,MATCH(M6,O6:AD6,)))

Hi again Fluff. I have discovered a minor problem with the formula for Cell N6.

For example, when I enter anything in the columns to the right after my last entry in the Item Columns (Q, V, AA or AF) then the value in the relevant N column shows a 0. I need N to show the last item entered in Q, V, AA or AF and only change it to when a new item is added to any of subsequent columns Q, V, AA, AF. So if column Q has an entry then N will show this item even after further data is entered in columns R, S, T, U and only change when an item is added in column V.

I hope I have explained this sufficiently and would greatly appreciate your help.

Thanks

 

[billszysz]

Hi billszysz, still unable to get your formulas to work. But I think FLUFF has solved it. Thank you for trying to help me.

You have to validate the formula by pressing the Ctrl + Shift + Enter keys simultaneously (this is an array formula)