matching a non-blank

i need to find the cell in a (single row) range that is not blank and return
the cell reference of the cell one row above

e.g.
a b c d
1 jan feb mar apr
2 xxx


would give me b1 - which i then need to use in another formula

however, the data in row 2 is not constant, ie can be numbers or text which
changes (via database query).

How can i do this?

Cheers
JulieD

Hi Julie

One way:

=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)

An array formula to be entered with ShiftCtrlEnter,
also if edited later.

The formula will return the contents of cell B1.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
i need to find the cell in a (single row) range that is not blank and
return
the cell reference of the cell one row above

e.g.
a b c d
1 jan feb mar apr
2 xxx


would give me b1 - which i then need to use in another formula

however, the data in row 2 is not constant, ie can be numbers or text
which
changes (via database query).

How can i do this?

Cheers
JulieD

hi Leo

thanks for this ... is there any possiblity of doing this twice in the same
range

for example

G1 = the first occurance of the non-blank (using formula provided below)
H1 = the second occurance of the non-blank

there will only ever be a maximum of 2 non-blanks (but may only be one)

Cheers
JulieD

"Leo Heuser" wrote in message
...
Hi Julie

One way:

=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)

An array formula to be entered with ShiftCtrlEnter,
also if edited later.

The formula will return the contents of cell B1.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
i need to find the cell in a (single row) range that is not blank and
return
the cell reference of the cell one row above

e.g.
a b c d
1 jan feb mar apr
2 xxx


would give me b1 - which i then need to use in another formula

however, the data in row 2 is not constant, ie can be numbers or text
which
changes (via database query).

How can i do this?

Cheers
JulieD

You're welcome, Julie.

This array formula will return the last occurrence
(first one, if only one exists, second one, if two exist)
and #REF! if none exists.

=OFFSET(A1,0,MAX(IF(ISBLANK(A2:H2)+0,-1,
(COLUMN(A1:H1)-COLUMN(A1)))))


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
hi Leo

thanks for this ... is there any possiblity of doing this twice in the
same
range

for example

G1 = the first occurance of the non-blank (using formula provided below)
H1 = the second occurance of the non-blank

there will only ever be a maximum of 2 non-blanks (but may only be one)

Cheers
JulieD

"Leo Heuser" wrote in message
...
Hi Julie

One way:

=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)

An array formula to be entered with ShiftCtrlEnter,
also if edited later.

The formula will return the contents of cell B1.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
i need to find the cell in a (single row) range that is not blank and
return
the cell reference of the cell one row above

e.g.
a b c d
1 jan feb mar apr
2 xxx


would give me b1 - which i then need to use in another formula

however, the data in row 2 is not constant, ie can be numbers or text
which
changes (via database query).

How can i do this?

Cheers
JulieD

Also for the value associated with the last (non-error) value in the range
of interest...

=LOOKUP(REPT("z",255),IF(A2:H2"",A2:H2&"",A22) ,$A$1:$H$1)

which must be confirmed with control+shift+enter.

This one will ignore error values in the target range, while sensitive to
any other data type.

If the range is empty, the result will be #N/A.

"Leo Heuser" wrote in message
...
You're welcome, Julie.

This array formula will return the last occurrence
(first one, if only one exists, second one, if two exist)
and #REF! if none exists.

=OFFSET(A1,0,MAX(IF(ISBLANK(A2:H2)+0,-1,
(COLUMN(A1:H1)-COLUMN(A1)))))


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
hi Leo

thanks for this ... is there any possiblity of doing this twice in the
same
range

for example

G1 = the first occurance of the non-blank (using formula provided below)
H1 = the second occurance of the non-blank

there will only ever be a maximum of 2 non-blanks (but may only be one)

Cheers
JulieD

"Leo Heuser" wrote in message
...
Hi Julie

One way:

=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)

An array formula to be entered with ShiftCtrlEnter,
also if edited later.

The formula will return the contents of cell B1.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
i need to find the cell in a (single row) range that is not blank
and
return
the cell reference of the cell one row above

e.g.
a b c d
1 jan feb mar apr
2 xxx


would give me b1 - which i then need to use in another formula

however, the data in row 2 is not constant, ie can be numbers or
text
which
changes (via database query).

How can i do this?

Cheers
JulieD

"Leo Heuser" wrote...
....
=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)
....

Why waste cycles converting ISBLANK's results to numbers?

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1)

Typo: D2 -- H2

"Aladin Akyurek" wrote in message
...
Also for the value associated with the last (non-error) value in the range
of interest...

=LOOKUP(REPT("z",255),IF(A2:H2"",A2:H2&"",A22) ,$A$1:$H$1)

which must be confirmed with control+shift+enter.

This one will ignore error values in the target range, while sensitive to
any other data type.

If the range is empty, the result will be #N/A.

"Leo Heuser" wrote in message
...
You're welcome, Julie.

This array formula will return the last occurrence
(first one, if only one exists, second one, if two exist)
and #REF! if none exists.

=OFFSET(A1,0,MAX(IF(ISBLANK(A2:H2)+0,-1,
(COLUMN(A1:H1)-COLUMN(A1)))))


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
hi Leo

thanks for this ... is there any possiblity of doing this twice in the
same
range

for example

G1 = the first occurance of the non-blank (using formula provided
below)
H1 = the second occurance of the non-blank

there will only ever be a maximum of 2 non-blanks (but may only be
one)

Cheers
JulieD

"Leo Heuser" wrote in message
...
Hi Julie

One way:

=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)

An array formula to be entered with ShiftCtrlEnter,
also if edited later.

The formula will return the contents of cell B1.


--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"JulieD" skrev i en meddelelse
...
i need to find the cell in a (single row) range that is not blank
and
return
the cell reference of the cell one row above

e.g.
a b c d
1 jan feb mar apr
2 xxx


would give me b1 - which i then need to use in another formula

however, the data in row 2 is not constant, ie can be numbers or
text
which
changes (via database query).

How can i do this?

Cheers
JulieD

"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
...
=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)
...

Why waste cycles converting ISBLANK's results to numbers?

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1)

To make it independent of localized names for TRUE.
For the same reason I use 0 instead of FALSE in
VLOOKUP(), HLOOKUP() and MATCH().
The cycles are, as you put it, wasted, that is, no one will ever
know, that they were there at all g

LeoH

Leo Heuser wrote:
"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
...
=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)
...

Why waste cycles converting ISBLANK's results to numbers?

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1)

To make it independent of localized names for TRUE.
For the same reason I use 0 instead of FALSE in
VLOOKUP(), HLOOKUP() and MATCH().
The cycles are, as you put it, wasted, that is, no one will ever
know, that they were there at all g

Hi Leo
in this case no need for this as excel converts TRUE/FALSE
automatically to their local substitutes.
So though I hate to admit, Harlan is right with these enormous waste of
cycles vbg

Frank

"Frank Kabel" skrev i en meddelelse
...
Leo Heuser wrote:
"Harlan Grove" skrev i en meddelelse
...
"Leo Heuser" wrote...
...
=OFFSET(A1,0,MATCH(0,ISBLANK(A22)+0,0)-1)
...

Why waste cycles converting ISBLANK's results to numbers?

=OFFSET(A1,0,MATCH(TRUE,ISBLANK(A22),0)-1)

To make it independent of localized names for TRUE.
For the same reason I use 0 instead of FALSE in
VLOOKUP(), HLOOKUP() and MATCH().
The cycles are, as you put it, wasted, that is, no one will ever
know, that they were there at all g

Hi Leo
in this case no need for this as excel converts TRUE/FALSE
automatically to their local substitutes.
So though I hate to admit, Harlan is right with these enormous waste of
cycles vbg

Frank

Hi Frank

Sorry, but you are wrong.

If I use the formula with the Danish function names, it reads

=FORSKYDNING(A1,0,SAMMENLIGN(TRUE,ER.TOM(A22),0)-1)

and I get a name error (for TRUE).

I have to use the Danish equivalent for TRUE, which is SAND

=FORSKYDNING(A1,0,SAMMENLIGN(SAND,ER.TOM(A22),0)-1)

What *are* converted automatically are the TRUEs/FALSEs in the array,
and that's precisely why you have to use the localized name for TRUE/FALSE,
and that's why I use 1 and 0 instead.
I doubt, that the German edition, is different, but I may be wrong.

LeoH