Dear all,



Kindly help me to create a formula for reference last non empty cell in row.
I have attached a LOG file and there is a column named CURRENT STATUS, here the current status of documents should be the latest status of document in the same row.
I want to do it by creating a formula so that this field will be updated automatically according the last cell. There are two fields: Current Status and Status date- I want both field to be updated using excel formula.
Kind check in the attached file.

Now I am updating CURRENT STATUS field manually but it is annoying while updating to many documents.

I highly appreciate you time and help.

Thank you very much.

Attached Files

LOG 2.xlsx (139.7 KB, 11 views)

Attached a file.


Edit:

Please change the formula in U9 to:

=LOOKUP(2,1/(ISTEXT($V10:$XFD10)),$V10:$XFD10)

and copy down.

Attached Files

LOG_2.xlsx (149.0 KB, 10 views)

This is not working. Please help.

If you want help, please be yourself a bit helpful instead of just stating a one-liner that it is not working.

As far as I can see it worked fine in the file I uploaded. If you mean the opposite you must at least tell what's the problem.

Dear XOR

According the file that you sent me, there is some problem. I specifying the problem by opening the LOG file.
When you open that file, there is a column named UNIFIER RES - this is only for the reference number of online uploaded file. It field should be filled manually but when I input the number there then DATE OF CURRENT STATUS gets changed. This is one problem and another problem is:
when I input DATE SUBMIT in 1ST SUBMISSION (REV. 00) then the STATUS DATE of CURRENT STATUS does not get changed. Other things are okey.

I hope you will solve it. I highly appreciate you valuable help and effort.
Thank you very very much.

OK, think I understand at least some of the problem.

Can you say something more precisely about the number in UNIFIER RES (within a certain interval, or number of digits or other charateristic?

I am in doubt what you mean by:
when I input DATE SUBMIT in 1ST SUBMISSION (REV. 00) then the STATUS DATE of CURRENT STATUS does not get changed.

If you enter a date in say say W11 shouldn't the formula in V11 still return the value in AE11?

Please elaborate.

Dear XOR,

I work in a construction office and we submit transmittal to Consultant for approval.
And UNIFIER is a ONLINE PROJECT MANAGEMENT SOFTWARE. We submit transmittal online through UNIFIER not hard copy. So while uploading files UNIFIER gives a unique reference number like TRN-00276. And this reference number may be different for every submission. For example: it may be one for Y13 and another for AC13. It field should be updated manually after uploading the file to UNIFIER.

I have attached the LOG_3 file.
There in the cell W13 - which is the date of submission. I input the date in W13 but V13 did not get changed. This is the very problem.

When I get respond from CONSULTANT, I input the date in X13.

So if there is submission date only- the date of current status should be same as submission date. And there is also respond date the date of current status should be same as respond date. So which is latest date, that should be the date of current status. It has the problem only in 1st Submission field that is W13- others are working well.

Thank you very much again. I hope you will surely find a solution for me.

Attached Files

LOG_3.xlsx (140.7 KB, 7 views)

Deleted again.

Yes XOR,

Y8, AC8, AG8 and AK8 are the same. They contain only reference number like TRN-0865.
They contain both Letters and numbers and also dash.
so UNIFIER RES and TRN NO. is the same.

Thank you very much XOR for your time and effort.

I am still not quite sure, but you may want to test the attached.

Attached Files

LOG_4.xlsx (153.8 KB, 7 views)

Thank you very much XOR.

Now it is working perfectly. I am so happy. Thank you very very much. You solved my problem of long time ago. You saved my time and effort.

I think only saying THANK YOU is not enough.
Anyway thank you very much.