Excel 2002

I need to return in column Z the last non-blank value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first non-
blank working backward from column z and hitting every
other column].

I can use nested IF statements using ISBLANK(X3), but only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible) could I
use to accomplish this?

Thanks for any help!

Create the following function in a standard module:

Function LastValue(r As Range)
With r
LastValue = .Parent.Cells(65536, .Column).End(xlUp).Value
End With
End Function

In the Z column enter the following to get the last value in column D

=LastValue(D:D)

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in message ...
Excel 2002

I need to return in column Z the last non-blank value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first non-
blank working backward from column z and hitting every
other column].

I can use nested IF statements using ISBLANK(X3), but only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible) could I
use to accomplish this?

Thanks for any help!

Hi Allison,
Try this
=IF(ISBLANK(D1),"
",INDEX(D1:P1,1,13-2*(ISBLANK(D1)+ISBLANK(F1)+ISBLANK(H1)+ISBLANK(J1) +ISBLAN
K(L1)+ISBLANK(N1)+ISBLANK(P1))))
It is not pretty but it works. I tried for a neater solution but wanted
something general since we know nothing about the intervening cells. Maybe
the true gurus will give us a better solution.
Bernard

"Allison" wrote in message
...
Excel 2002

I need to return in column Z the last non-blank value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first non-
blank working backward from column z and hitting every
other column].

I can use nested IF statements using ISBLANK(X3), but only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible) could I
use to accomplish this?

Thanks for any help!

This VBA was great for returning the last value in a
column, but that's not what I wanted. Thanks anyway for
trying to help.

Allison

-----Original Message-----
Create the following function in a standard module:

Function LastValue(r As Range)
With r
LastValue = .Parent.Cells(65536, .Column).End
(xlUp).Value
End With
End Function

In the Z column enter the following to get the last value
in column D

=LastValue(D:D)

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in
message ...
Excel 2002

I need to return in column Z the last non-blank value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first non-
blank working backward from column z and hitting every
other column].

I can use nested IF statements using ISBLANK(X3), but
only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible) could
I
use to accomplish this?

Thanks for any help!


.

This returned a value of "37741" no matter which cell I
put it in. That won't work either.

Thanks so much for trying.

Allison


-----Original Message-----
Hi Allison,
Try this
=IF(ISBLANK(D1),"
",INDEX(D1:P1,1,13-2*(ISBLANK(D1)+ISBLANK(F1)+ISBLANK(H1)
+ISBLANK(J1)+ISBLAN
K(L1)+ISBLANK(N1)+ISBLANK(P1))))
It is not pretty but it works. I tried for a neater
solution but wanted
something general since we know nothing about the
intervening cells. Maybe
the true gurus will give us a better solution.
Bernard

"Allison" wrote in
message
...
Excel 2002

I need to return in column Z the last non-blank value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first non-
blank working backward from column z and hitting every
other column].

I can use nested IF statements using ISBLANK(X3), but
only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible) could
I
use to accomplish this?

Thanks for any help!


.

Allison,

What exactly do you want? Perhaps you mean that you want the value from the left hand side of the Z column that is closest to the Z
column for each row. If so, and if the odd columns are always empty, the following version of my function would work:

Function LastValue(r As Range)
LastValue = Application.Caller.End(xlToLeft).Value
End Function

where you would put the following in Z6, for example

=LastValue(D6:X6)

If there are values in the odd columns, this will probably not work.

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in message ...
This VBA was great for returning the last value in a
column, but that's not what I wanted. Thanks anyway for
trying to help.

Allison

-----Original Message-----
Create the following function in a standard module:

Function LastValue(r As Range)
With r
LastValue = .Parent.Cells(65536, .Column).End
(xlUp).Value
End With
End Function

In the Z column enter the following to get the last value
in column D

=LastValue(D:D)

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in
message ...
Excel 2002

I need to return in column Z the last non-blank value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first non-
blank working backward from column z and hitting every
other column].

I can use nested IF statements using ISBLANK(X3), but
only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible) could
I
use to accomplish this?

Thanks for any help!


.

That is what I want, but only from every other cell to the
left. I tried your VBA code and it works great for
returning the closest value to my final column, but I need
it to ignore every other column.

Thanks for trying to help.

Allison

-----Original Message-----
Allison,

What exactly do you want? Perhaps you mean that you want
the value from the left hand side of the Z column that is
closest to the Z
column for each row. If so, and if the odd columns are
always empty, the following version of my function would
work:

Function LastValue(r As Range)
LastValue = Application.Caller.End(xlToLeft).Value
End Function

where you would put the following in Z6, for example

=LastValue(D6:X6)

If there are values in the odd columns, this will
probably not work.

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in
message ...
This VBA was great for returning the last value in a
column, but that's not what I wanted. Thanks anyway for
trying to help.

Allison

-----Original Message-----
Create the following function in a standard module:

Function LastValue(r As Range)
With r
LastValue = .Parent.Cells(65536, .Column).End
(xlUp).Value
End With
End Function

In the Z column enter the following to get the last
value
in column D

=LastValue(D:D)

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote
in
message ...
Excel 2002

I need to return in column Z the last non-blank
value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first
non-
blank working backward from column z and hitting
every
other column].

I can use nested IF statements using ISBLANK(X3), but
only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible)
could
I
use to accomplish this?

Thanks for any help!


.



.

Function LastValue(r As Range)
Dim i As Integer

i = 2
With Application.Caller

Do Until .Offset(0, -i).Value < "" Or .Offset(0, -i).Column = 4
i = i + 2
Loop

LastValue = .Offset(0, -i).Value

End With

End Function


--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in message ...
That is what I want, but only from every other cell to the
left. I tried your VBA code and it works great for
returning the closest value to my final column, but I need
it to ignore every other column.

Thanks for trying to help.

Allison

-----Original Message-----
Allison,

What exactly do you want? Perhaps you mean that you want
the value from the left hand side of the Z column that is
closest to the Z
column for each row. If so, and if the odd columns are
always empty, the following version of my function would
work:

Function LastValue(r As Range)
LastValue = Application.Caller.End(xlToLeft).Value
End Function

where you would put the following in Z6, for example

=LastValue(D6:X6)

If there are values in the odd columns, this will
probably not work.

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote in
message ...
This VBA was great for returning the last value in a
column, but that's not what I wanted. Thanks anyway for
trying to help.

Allison

-----Original Message-----
Create the following function in a standard module:

Function LastValue(r As Range)
With r
LastValue = .Parent.Cells(65536, .Column).End
(xlUp).Value
End With
End Function

In the Z column enter the following to get the last
value
in column D

=LastValue(D:D)

--

John Green - Excel MVP
Sydney
Australia


"Allison" wrote
in
message ...
Excel 2002

I need to return in column Z the last non-blank
value in
columns D, F, H, J, L, N, P, R, T, V, X (every other
column starting with D) [or conversely, the first
non-
blank working backward from column z and hitting
every
other column].

I can use nested IF statements using ISBLANK(X3), but
only
up to 7 and I need 11.

What formula (or VBA if a formula isn't possible)
could
I
use to accomplish this?

Thanks for any help!


.



.