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.

 

[billszysz]

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

check this out (N6, CSE formula so you have to validate it by pressing CTRL +SIHFT + ENTER)

=IF(M6="NO ENTRIES", IF(O6="Do Not Use", "Do Not Use","NO DATA"),   LOOKUP("zzzzz",CHOOSE({1;2;3;4},Q6,V6,AA6,AF6) )   )

 

[Detectiveclem]

check this out (N6, CSE formula so you have to validate it by pressing CTRL +SIHFT + ENTER)

=IF(M6="NO ENTRIES", IF(O6="Do Not Use", "Do Not Use","NO DATA"),   LOOKUP("zzzzz",CHOOSE({1;2;3;4},Q6,V6,AA6,AF6) )   )

Hi billszysz, I have been successfully using the formula that Fluff suggested (see earlier post) please disregard the ‘Do Not Use’ entry. I simply want to amend Fluff’s formula but as I said if fields are populated between my item fields at Q, V, AA or AF, I do not want N to show ‘0’ result but N still shows the last entry made whether it be at Q, V, AA or AF.

If you are able to help with this I would be extremely grateful. Thanks

 

[billszysz]

Hi billszysz, I have been successfully using the formula that Fluff suggested (see earlier post) please disregard the ‘Do Not Use’ entry. I simply want to amend Fluff’s formula but as I said if fields are populated between my item fields at Q, V, AA or AF, I do not want N to show ‘0’ result but N still shows the last entry made whether it be at Q, V, AA or AF.

If you are able to help with this I would be extremely grateful. Thanks

Please, check this file. Is that what you want?Test file

 

[Fluff]

You should only get a 0 in col N if one of the Date Sent columns has a date & the corresponding Item is blank.

 

[Detectiveclem]

You should only get a 0 in col N if one of the Date Sent columns has a date & the corresponding Item is blank.

Hi Fluff,

Sorry I am unable to upload from work due to restrictions, but here is a screen shot (see N9 and N11) where further data is added after the populated item column but no further item is entered after this.

I always want N to show the last item entered no matter what is entered between the next ITEM COLUMN.

I hope this now makes sense.

Thank you

 

[Detectiveclem]

Please, check this file. Is that what you want?Test file

Hi, I am presently at work and my work computer will not allow downloads. I will try this when I get home. Please also see my reply to Fluff.

Thank you for trying to help, it is greatly appreciated.

 

[Fluff]

That suggest you have the wrong formula in M, as it works correctly for me

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
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	21/03/2021	FORM C	01/01/2021	Post	FORM A	02/02/2021	NO	02/01/2021	Email	Message 1	21/01/2021	NO	03/02/2021	email	FORM B	10/02/2021	no	21/03/2021	EMAIL	FORM C
7	01/01/2021	FORM A	01/01/2021	Post	FORM A
8	05/02/2021	FORM B	02/01/2021	Post	FORM A	03/02/2021	NO	05/02/2021	Post	FORM B
9	03/02/2021	Message 2	03/01/2021	Post	FORM A	02/02/2021	NO	02/01/2021	Email	Message 1	21/01/2021	NO	03/02/2021	email	Message 2	26/02/2021
10	NO ENTRIES	NO DATA
11	05/01/2021	FORM A	05/01/2021	Post	FORM A	02/03/2021	YES		ppp
12	06/01/2021	FORM A	06/01/2021	Post	FORM A	08/01/2021	Yes
Master

Cell Formulas
Range	Formula
M6:M12	M6	=IFERROR(1/(1/AGGREGATE(14,6,O6:AD6/($O$5:$AD$5="Date Sent"),1)),"NO ENTRIES")
N6:N12	N6	=IF(M6="No entries","NO DATA",INDEX(Q6:AF6,MATCH(M6,O6:AD6,0)))

 

[Detectiveclem]

That suggest you have the wrong formula in M, as it works correctly for me

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
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	21/03/2021	FORM C	01/01/2021	Post	FORM A	02/02/2021	NO	02/01/2021	Email	Message 1	21/01/2021	NO	03/02/2021	email	FORM B	10/02/2021	no	21/03/2021	EMAIL	FORM C
7	01/01/2021	FORM A	01/01/2021	Post	FORM A
8	05/02/2021	FORM B	02/01/2021	Post	FORM A	03/02/2021	NO	05/02/2021	Post	FORM B
9	03/02/2021	Message 2	03/01/2021	Post	FORM A	02/02/2021	NO	02/01/2021	Email	Message 1	21/01/2021	NO	03/02/2021	email	Message 2	26/02/2021
10	NO ENTRIES	NO DATA
11	05/01/2021	FORM A	05/01/2021	Post	FORM A	02/03/2021	YES		ppp
12	06/01/2021	FORM A	06/01/2021	Post	FORM A	08/01/2021	Yes
Master

Cell Formulas
Range	Formula
M6:M12	M6	=IFERROR(1/(1/AGGREGATE(14,6,O6:AD6/($O$5:$AD$5="Date Sent"),1)),"NO ENTRIES")
N6:N12	N6	=IF(M6="No entries","NO DATA",INDEX(Q6:AF6,MATCH(M6,O6:AD6,0)))

Hi Fluff, you are most probably right, although I think I copied and pasted it. I will copy it again and let you know if it was down to my own stupidity. I won’t be able to try it until tomorrow now, but I am really thankful for your help.

 

[Detectiveclem]

That suggest you have the wrong formula in M, as it works correctly for me

+Fluff 1.xlsm
	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
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	21/03/2021	FORM C	01/01/2021	Post	FORM A	02/02/2021	NO	02/01/2021	Email	Message 1	21/01/2021	NO	03/02/2021	email	FORM B	10/02/2021	no	21/03/2021	EMAIL	FORM C
7	01/01/2021	FORM A	01/01/2021	Post	FORM A
8	05/02/2021	FORM B	02/01/2021	Post	FORM A	03/02/2021	NO	05/02/2021	Post	FORM B
9	03/02/2021	Message 2	03/01/2021	Post	FORM A	02/02/2021	NO	02/01/2021	Email	Message 1	21/01/2021	NO	03/02/2021	email	Message 2	26/02/2021
10	NO ENTRIES	NO DATA
11	05/01/2021	FORM A	05/01/2021	Post	FORM A	02/03/2021	YES		ppp
12	06/01/2021	FORM A	06/01/2021	Post	FORM A	08/01/2021	Yes
Master

Cell Formulas
Range	Formula
M6:M12	M6	=IFERROR(1/(1/AGGREGATE(14,6,O6:AD6/($O$5:$AD$5="Date Sent"),1)),"NO ENTRIES")
N6:N12	N6	=IF(M6="No entries","NO DATA",INDEX(Q6:AF6,MATCH(M6,O6:AD6,0)))

Thank you Fluff, it must have been me inputting the formula incorrectly. Everything is working perfectly now. Thanks for solving this for me, you are a complete wizard with Excel. I can't thank you enough. Have a great day.

 

[Detectiveclem]

Thank you Fluff, it must have been me inputting the formula incorrectly. Everything is working perfectly now. Thanks for solving this for me, you are a complete wizard with Excel. I can't thank you enough. Have a great day.

Hi Fluff, sorry have have just discovered another slight hip-up in Column N.

If for example the received date entered in Column R is the same as the date entered in the Date sent column T the the result in the corresponding cell in N shows a number such as 44343 rather than the last item entered. This only happens when the two dates are the same.

This is a fairly frequent occurrence as often we will receive a response triggering us to enter the Rec'd date back column and then send out an item the same day.

Is there any way this issue can be resolved please?

Thanks again