Need to find the right function code

[Guest]

Hi,

need to find the right function code ...

In a separate cell, I want to display either a number or a text found in a
single cell from a range of cells, its a one to one relationship, i.e. in
cell C4 show the entry (either a text or a number) that may be in one cell
from D4 through G4.

I'm stumped ;-)

Thanks

 

[Guest]

"Tony V" wrote:
Give a better example and maybe someone could help
I'm not really sure what you need.

 

[Guest]

In Excel, I have 25 rows and 12 columns. I would like to add a row with a
function that queries six cells in the same row to determine which cell
contains data. As a rule, only one cell of the six has data. The other cells
are blank. The data can be numeric or alpha numeric. I simply want to querie
the range, find the cell with data in it and display it.

 

[Dave Peterson]

If the value were always numeric, you could use:
=sum(A1:F1)
if the value were always text, you could use:
=a1&b1&c1&d1&e1&f1

But the first will fail if the single value is text (you'll see 0).
The second will return text even if the value is a number.

Maybe you could live with those restrictions?


If you can't, then there are other formulas:

=index(a1:f1,match(true,a1:f1<>"",0))
or
=if(counta(a1:f1)<>1,"not just one value!",index(a1:f1,match(true,A1:f1<>"",0)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

But this will fail if those six cells aren't contiguous.

 

[Guest]

Mr. Peterson.

Thank you very much,

the function code "=if(counta(a1:f1)<>1,"not just one
value!",index(a1:f1,match(true,A1:f1<>"",0)))"

worked, it is exactly what I was looking for.

Thank you very much and I appreciate your consult! Let me know if how I can
repay you for your effort!

Thank you.

 

[Dave Peterson]

If you hang around the newsgroups and see a post that needs an answer and you
know it, post it.

Or just help someone out in real life.

Mr. Peterson.

Thank you very much,

the function code "=if(counta(a1:f1)<>1,"not just one
value!",index(a1:f1,match(true,A1:f1<>"",0)))"

worked, it is exactly what I was looking for.

Thank you very much and I appreciate your consult! Let me know if how I can
repay you for your effort!

Thank you.

 

[Guest]

Touche'

Will help out someone in real life and on your behalf!

Ciao, buona fortuna