Return value of heading if an adjacent cell in a row is not blank

[Guest]

I need some help/ideas in creating a macro or subroutine that returns the
column heading of the last cell in a row series that is not blank, and
populates the column heading in a cell adjacent to the last task date.

I'm using a worksheet to track the dates of when a particular task is
completed, and then I want to know what was the last task completed in this
series in the cell next to the last task on that row. The data looks like
this:


Task 1 Task 2 Task 3 Task 4 Task 5 Last Task
completed
4/1 4/5 4/6 4/8
Task 4
3/8 3/7
Task 2
3/15 3/17 3/18 3/22 3/29
Task 5
4/12 4/14 4/15
Task 3

....and so on.

What I'd like to do is have Excel check the row to see if it has a date
value and then go to the next cell until it finds one that is blank. If it
is blank, then return the column heading (task) of the last cell that was not
blank in the appropriate cell (in this case "Last Task Completed".

I'm using Excel 2000 and I'm comfortable using Excel, but beyond writing
simple macros and functions - I'm somewhat limited as I'm not familiar with
writing VBA code.

So any ideas or suggestions will be greatly appreciated!

 

[Don Guillett]

How about a formula that tries to match any impossible number on row 3 for
row 1.
=INDEX(1:1,1,MATCH(99999999999,3:3))

 

[Tushar Mehta]

Suppose your data are laid out starting in cell A1. That means that
'Task 5' is in cell E1.

Now, if the tasks can be completed only in sequence (i.e., 1 must
precede 2, which must precede 3, etc.), then the formula, in F2,
=OFFSET($A$1,0,COUNTA(A2:E2)-1,1,1) gives you the desired result. Copy
F2 as far down column F as required.

Alternatively, if tasks can be completed in any sequence, use the array
formula =INDEX($A$1:$E$1,MAX(COLUMN(A2:E2)*(A2:E2<>"")))

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Guest]

Thank you for taking time to respond to my posting. Tushar's solution is the
one that produces the result that I'm looking for. Again, thank you for your
help.