Returning a value based on the last edited cell in a row

[excelhelp15]

Hi

Please can you help me. I have basic excel knowledge but need to set up a covid register for lateral testing in our schools. You will see from the spreadsheet the columns and rows I have. The yellow highlighted columns I am hoping someone can help with auto populating them based on formula but I have no idea how to do this.

I will be populating cells (hopefully) with the letter N for Negative. The tests will happen either daily or weekly so all staff will have different dates - therefore I want a cell to return the very last date of that member of staffs test result so I can filter that column quickly.

I also want to autofill the last test result in a column.

I've attached my very basic spreadsheet.

[AliGW]

Which version of Excel are you using?

[excelhelp15]

Hi Ali

Excel 2016

[AliGW]

In C3 copied down:

=IFNA(IF(LOOKUP(2,1/($D3:$Q3<>0),$D3:$Q3)="N","Negative","Positive"),"")

[excelhelp15]

Wow - Ali thank you it worked. Thank you - can you help with the last date of test?

[AliGW]

Thanks - please update your forum profile, as it's out-of-date. My solution is in post #3 above.

[AliGW]

Yes - please update your forum profile. I'll be back with an answer in a mo.

[AliGW]

In B3 copied down:

=IFNA(LOOKUP(2,1/($D3:$Q3<>0),$D$2:$Q$2),"")

Some of your dates are text and not real dates. Change them to real dates to fix the formatting.

[excelhelp15]

THANK YOU SO MUCH

All sorted - amazing. I've updated my profile

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[AliGW]

You didn't mark it as solved correctly - see my instructions above. I'll amend it for you today.