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]

How about

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
5	Date of last transaction	Last Item	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete	Date	Item	Method	Complete
6	02/03/2021	Email	01/01/2021	Letter	Post	NO	02/03/2021	Email	External email	YES
7	10/01/2021	Application	10/01/2021	Application	Courier	YES
8	25/03/2021	Application	12/02/2021	Email	External email	NO	14/02/2021	Letter	Post	NO	25/03/2021	Application	Courier	YES
9	25/06/2021	Letter	12/02/2021	Letter	Post	NO	05/03/2021	Application	Courier	NO	30/03/2021	Letter	Post	NO	25/06/2021	Letter	Post	Yes
Master

Cell Formulas
Range	Formula
M6:M9	M6	=AGGREGATE(14,6,O6:AD6,1)
N6:N9	N6	=INDEX(P6:AE6,MATCH(M6,O6:AD6,0))

Hi Fluff I have tried both formulas you suggest and we are almost there. I had to change the INDEX formula slightly to the following =INDEX(Q6:AF6,MATCH(M6,O6:AD6,)).

As you'll see from the screen print below, I still have a couple of issues I would appreciate help with.

1. How do I stop the cell in Column M and N displaying #NUM! if no fields have been populated (see M10 & N10 below)
2. As you can see at N11 the result changes from showing the correct details 'FORM A' to '0' when I add the received back date and shown YES in the complete column. I need this to remain as FORM A as it was the last item sent.

 

[billszysz]

I do not like working with pictures...give us a real table (part of it of course)

 

[billszysz]

M6

=MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5)))

N6

=INDEX(O6:AH6,MATCH(M6,O6:AH6,0)+2)

 

[billszysz]

Taking into account the blank rows would be so
M6

=LET( max, MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5))),
IF(max,max,""))

N6

=IF(M6="","",(INDEX(O6:AH6,MATCH(M6,O6:AH6,0)+2)))

 

[Detectiveclem]

M6

=MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5)))

N6

=INDEX(O6:AH6,MATCH(M6,O6:AH6,0)+2)

Hi Billszysz,

I tried your formulas but they still have issues / errors.

 

[Detectiveclem]

I am trying to upload using XL2BB, but my mini sheet or table options are greyed out. I will keep trying.

 

[Detectiveclem]

Book1.xlsx
	F
16
Sheet1

 

[Detectiveclem]

I hope this has uploaded properly

 

[billszysz]

If you do not have an Excel 365 then for M6 use this formula below

=IF( MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5))), MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5))),"")

ups....sorry, SEQUENCE doesnt exist in other wersion of excel

 

[Detectiveclem]

If you do not have an Excel 365 then for M6 use this formula below

=IF( MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5))), MAX(INDEX(O6:AH6,SEQUENCE(COLUMNS(O6:AH6)/5,,1,5))),"")

I need to use it on Excel 2016