FUNCTION - more Activity in Rows and pass the result of the last result in start cell

[Ksaa_Eone]

STATUS ! Activity_1 Date Activity_2 Date Activity_3 Date Activity_4 Date
Activity_3-DONE ! AB 01-02-20 CD 02-Feb EF 03-Feb

How to write FUNCTION if there are more Activity in Rows, excel sheet attached:

1) Above are the Activity data in 1st rows as header i.e : Activity_1, Activity_2, Activity_3, Activity_4 …...
2) I want to find in the cell STATUS as Activity_3-DONE, based on the Date entered in the cell ie. the last Activity & Date done.
3) To explain as per reference to Excel sheet attached:
a) The Last Activity done is Activity_3 (Column-H) on date 3-Feb (Column-I)
b) Automatically I get in STATUS (Column-C2) in Cell C2 Activity_3-DONE



Regards,

[José Augusto]

Try this in B2

Please Login or Register  to view this content.

[Ksaa_Eone]

Thanks Jose,

you are great boss!!

But Activity column and Date should not be Empty

Regards,

[Ksaa_Eone]

Jose,

Sorry the explanation goes like this....

there should be validation check that cell of Activity column and Date should not be Empty then only the cell STATUS should have the answer.

I think too much using function therefore i am not able to get the correct function...

Regards,

[José Augusto]

Hi
I suppose you want this

Please Login or Register  to view this content.

[Ksaa_Eone]

Jose,

Great !!

=INDEX(C1:AN1,MATCH(AGGREGATE(14,6,D2:AN2*--(C2:AM2<>""),1),C2:AN2,0)-1)&"-DONE"

This works great, but I realized, if any of the earlier column are null/without any value/data,
still it shows the last entered data in the Result Cell.

eg: Activity_3 Date is Blank

but this is
Activity_4 Date
KI 04-Feb

Which is technically / logically need to be avoided. It should can give message as "Activity Balance" in Result cell.

Please help to re-write the function. Excel sheet is attached with two cells marked in RED.

Regards

[Ksaa_Eone]

Jose,

Just to understand the requirement :

the Activity_1/Date, Activity_2/Date, Activity_3/Date, Activity_4/Date.... are incremental steps, Activity_4/Date can not be performed unless Activity_3/Date is done. Therefore need the Function with the control null/empty cell value.

This is the basic concept of this excel sheet.

Regards,

[JeteMc]

Please try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ksaa_Eone]

Hello JeteMc,

It is the same as details given as above, what actually I want is :

The Activity_1/Date, Activity_2/Date, Activity_3/Date, Activity_4/Date.... are incremental steps,

Activity_4/Date can not be performed unless Activity_3/Date is done.

Therefore need the Function with the control check to any of the cell should not be null/empty.

It should give result as the values until the last Activity Rows_cell are completely filled.

Hope I am able to communicate to you.

Regards

[JeteMc]

I assume that there are basically four scenarios.
1. No activities are filled
2. Consecutive activities are filled (i.e. activity_1 through activity_4)
3. There is a gap in the activities filled (as shown in the file attached to post #6)
4. All of the activities are filled
If you could either show or tell us what the Result cell should display in each scenario, then it may be easier to provide a formula to replicate that result.
Let us know if you have any questions.

[Ksaa_Eone]

1. No activities are filled --> TO START
2. Consecutive activities are filled (i.e. activity_1 through activity_4) --> LAST ACTIVITY DONE (i.e. Activity_4 Done)
3. There is a gap in the activities filled (as shown in the file attached to post #6) --> Activity(collect Activity number from the gap column) Balance
4. All of the activities are filled : The last Activity_4 Done

[JeteMc]

This proposal employs three relatively simple formulas as helpers in columns AO:AQ
The formula for AO2 is:

Please Login or Register  to view this content.

The formula for AP2 is:

Please Login or Register  to view this content.

The formula for AQ2 is:

Please Login or Register  to view this content.

The formula for B2 is:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Ksaa_Eone]

This is excellent.

Thanks for the logic used....

[bebo021999]

May I suggest a non helper solution?

Here is my approach:
(A) First blank cell position: MATCH(TRUE,INDEX($C2:$AN2="",),0)
(B) Last cell with date position: LOOKUP(2,1/(ISNUMBER($C2:$AN2)),COLUMN($C$2:$AN$2)-COLUMN($C$2)+1)

Algorithm:
If (A)-(B)=1, then case of DONE, else, case of BALANCE)

Finall formula:

Please Login or Register  to view this content.

[Ksaa_Eone]

Hi,

This is also great solution... Excellent and thanks !!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.