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

=MAX(E4:N4)

if all are blank except one cell
--
Gary''s Student - gsnu200736


"Sal" wrote:

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

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

--
Biff
Microsoft Excel MVP


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

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


"Gary''s Student" wrote:

=MAX(E4:N4)

if all are blank except one cell
--
Gary''s Student - gsnu200736


"Sal" wrote:

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

=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
--
Gary''s Student - gsnu200736


"Sal" wrote:

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


"Gary''s Student" wrote:

=MAX(E4:N4)

if all are blank except one cell
--
Gary''s Student - gsnu200736


"Sal" wrote:

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

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

--
Gary''s Student - gsnu200736


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

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)

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200736


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

For text (assuming there are no formula blanks):
=INDEX(A1:E1,MATCH("*",A1:E1,0))

Even shorter:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"Gary''s Student" wrote in
message ...
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

--
Gary''s Student - gsnu200736


"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


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

When no headings needed, how about:

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

Will give you text, numbers, logical, error

"Gary''s Student" wrote:

=MAX(E4:N4)

if all are blank except one cell
--
Gary''s Student - gsnu200736


"Sal" wrote:

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

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.

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


"Gary''s Student" wrote:

=MAX(E4:N4)

if all are blank except one cell
--
Gary''s Student - gsnu200736


"Sal" wrote:

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