Multiple formulae in one cell (vague title, sorry)

[swelldandy]

I have to create a work plan spreadsheet that tracks tasks, which have steps.

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Task X [/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Step 1[/TD]
[TD]1. Status 1[/TD]
[/TR]
[TR]
[TD]Step 2[/TD]
[TD]2. Status 2[/TD]
[/TR]
[TR]
[TD]Step 3[/TD]
[TD]3. Status 3[/TD]
[/TR]
[TR]
[TD]Step 4[/TD]
[TD]4. Status 4[/TD]
[/TR]
[TR]
[TD]Step 5[/TD]
[TD]5. Status 5[/TD]
[/TR]
[TR]
[TD]Step 6[/TD]
[TD]6. Status 6[/TD]
[/TR]
</tbody>[/TABLE]

As each staff member completes the step, they will update the status line, for example "DoF approval received 01/01/2019"

From there, I would like to have a sheet where each task was condensed down into the task and the latest status. For example:

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Task [/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]Task 1 [/TD]
[TD]4. DoF approval received 01/01/2019[/TD]
[/TR]
[TR]
[TD]Task 2[/TD]
[TD]3. Approval requested by DoF [/TD]
[/TR]
</tbody>[/TABLE]

Is it possible to ask Excel to look at line 6 (in table 1), and if blank, look at line 5 so on and so forth until it finds text at which point it copies the status? Or is there a better way to do it? Or can it not be done at all?

Thanks for your time.

 

[jtakw]

Hi,

This formula will return the Last Text entry in your range:

Book1
	A	B
1	Task 1	Status
2	Step 1	1. Status 1
3	Step 2	2. Status 2
4	Step 3	3. Status 3
5	Step 4	4. DoF approval received 01/01/2019
6	Step 5
7	Step 6
8
9
10	Task	Status
11	Task 1	4. DoF approval received 01/01/2019
Sheet617

Cell Formulas
Range	Formula
B11	=LOOKUP("zzz",B2:B7)

 

[swelldandy]

Hi,

This formula will return the Last Text entry in your range:

	A	B
Task 1	Status
Step 1	1. Status 1
Step 2	2. Status 2
Step 3	3. Status 3
Step 4	4. DoF approval received 01/01/2019
Step 5
Step 6
Task	Status
Task 1	4. DoF approval received 01/01/2019

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

</tbody>

Sheet617​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B11[/TH]
[TD="align: left"]=LOOKUP("zzz",B2:B7)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you so much, that's perfect

 

[jtakw]

You're welcome, welcome to the forum.