cell equal to range

[aspiringnerd]

Is it possible to have a cell equal to a range a1-a50 where only 1 cell within that range will have text and the rest are blank? if I formulate cell b1 as =a1:a50 then it also enters b2:b50 with zeroes which I don't want as it affects other data on that row.

 

[Joe4]

You cannot really set one cell equal to a whole range (it doesn't even really make sense, logically).
However, if you just want to see if the value in a certain cell is found within a range of cells, you can do that easily using the COUNTIF function.
Just count how many times that particular value occurs in that range. If the formula returns a value greater than 0, then that value is found in that cell.
If it returns 1, then it is found exactly once in that range.

See: MS Excel: How to use the COUNTIF Function (WS)

 

[aspiringnerd]

You cannot really set one cell equal to a whole range (it doesn't even really make sense, logically).
However, if you just want to see if the value in a certain cell is found within a range of cells, you can do that easily using the COUNTIF function.
Just count how many times that particular value occurs in that range. If the formula returns a value greater than 0, then that value is found in that cell.
If it returns 1, then it is found exactly once in that range.

See: MS Excel: How to use the COUNTIF Function (WS)

I'm not really counting though, occasionally the results will be numbers but a lot of the time it will just be text. I'm trying to capture a range of data that will mostly be blank except 1 cell. It will always just be 1 cell that isn't blank, but it's random.

I should also add, that there is no predictable pattern to what the text could contain.

 

[Joe4]

So what exactly is it that you are trying to ultimately do here?
Are you trying to identify where the non-empty cell is?
Or are you trying to get the value of this non-empty cell?

Once we have a better idea of what you are ultimately trying to do here, we may be able to come up with something that does what you want.

 

[aspiringnerd]

figured it, but it's long. I used the IFS function

 

[Joe4]

figured it, but it's long. I used the IFS function

I am willing to bet there is a better option, if you just answer my questions and let me know which version of Excel you are using.

 

[aspiringnerd]

I am willing to bet there is a better option, if you just answer my questions and let me know which version of Excel you are using.

I have a cell and I want it to equal the only non empty cell in a given range. I'm using 365

 

[aspiringnerd]

=INDEX(Sheet1!M2:CH2,MATCH(FALSE,ISBLANK(Sheets1!M2:CH2),0))

I've tried using this but it only ever returns the first value in the array if true. I tested other cells but returns a 0 in all other positions except position 1

 

[Joe4]

Have a look here: Find the First Non-blank Value in a Row

If you cannot get any of them to work, confirm that your other cells are really empty (and not a space or a 0).
Also, show us an example of your data.