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!
--
Thanks, Wil H.

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))

--
Don Guillett
SalesAid Software

"Wil H." wrote in message
...
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!
--
Thanks, Wil H.

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

In article ,
says...
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!

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.

"Tushar Mehta" wrote:

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

In article ,
says...
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!

Testing shows that all three formulas produce the same result. Tushars could
even be
=OFFSET($A$1,0,COUNTA(3:3)-1,1,1)
=INDEX(1:1,MAX(COLUMN(3:3)*(3:3<""))) cse
--
Don Guillett
SalesAid Software

"Wil H." wrote in message
...
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.

"Tushar Mehta" wrote:

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

In article ,
says...
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!