Return value of a non blank cell

[Guest]

Hi,

I am looking for a formula that will examine cells E4:N4 and return the
value of the one cell that is not blank in that range to cell P4.

Any ideas?

Thanks

 

[Guest]

=MAX(E4:N4)

if all are blank except one cell

 

[T. Valko]

What type of data is it? Is it text or a number or can it be either?

 

[Guest]

Thanks,

If I wanted to go one step further and say now give me the column heading of
the cell that contains a value.

Eg if the formula returns a value of 10 then how can I get Excel to tell me
what the column heading is of 10 ie which product code?

Thanks

 

[Guest]

=INDEX(E1:N4,1,MATCH(MAX(E4:N4),E4:N4))

the MATCH gives us the column
the INDEX gives us the content of row 1 for that column

 

[Guest]

You raise a good point. If it could be either, I would run back to VBA:

Function nblank(r As Range)
For Each rr In r
If Len(rr.Value) > 0 Then
nblank = rr.Value
Exit Function
End If
Next
nblank = ""
End Function

 

[T. Valko]

The reason I asked is that to a lot of folks "value" means number but to me
"value" means anything: text, number, logicals, errors.

Rather than suggest a generic formula like this that works for both text or
numbers:

=INDEX(A1:E1,MATCH(TRUE,INDEX(A1:E1<>"",,0),0))

If you know the specific data type you can use a shorter formula:

For example:

For numbers (your suggestion):

=MAX(A1:E1)

For text (assuming there are no formula blanks):

=INDEX(A1:E1,MATCH("*",A1:E1,0))

For text and excluding formula blanks:

=HLOOKUP("?*",A1:E1,1,0)

 

[T. Valko]

For text (assuming there are no formula blanks):

=INDEX(A1:E1,MATCH("*",A1:E1,0))

Even shorter:

=HLOOKUP("*",A1:E1,1,0)

 

[Guest]

When no headings needed, how about:

=CONCATENATE(A1,B1,C1,D1,E1)

Will give you text, numbers, logical, error

 

[Guest]

Thank you all for your responses - will give them all a try.

In answer to your question, the row information (where I am looking for non
blanks) is a numeric value and the column header I am after is text.

Thank you all very much for your time.

 

[Afia]

is this applicable for dates too? I'm looking at range in a row say n2:r2,
if there is a non blank cell with a date then I want it to populate in q2.
Can anyone help please?
thanks