Search cell range and return value dependant on position of non blank cell

[Dermotdjt]

Trying to work out how to search a cell range for a non blank cell and dependant on the position of the non blank cell return a set value.

So cell range B3:G3 should only have one non blank cell.
Cell range B10:G10 contian the values 9,19,29,39,49 & 59

if B3 is non blank return value from B10, ie 9
if C3 is non blank return value from c10, ie 19
etc

If this is possible, would it then be possible to return another value (ie ERR) if more than 1 cell in the range is non blank

Many thanks

 

[T. Valko]

Trying to work out how to search a cell range for a non blank cell and dependant on the position of the non blank cell return a set value.

So cell range B3:G3 should only have one non blank cell.
Cell range B10:G10 contian the values 9,19,29,39,49 & 59

if B3 is non blank return value from B10, ie 9
if C3 is non blank return value from c10, ie 19
etc

If this is possible, would it then be possible to return another value (ie ERR) if more than 1 cell in the range is non blank

Many thanks

What type of data is in B3:G3?

Maybe this...

=SUMIF(B3:G3,"<>",B10:G10)

 

[Dermotdjt]

HI Biff

That would seem to work. The data in the range will be non-numeric. It will probably just be an 'X', to show that that cell/column has been selected.

Also using you formula if more than 1 cell in the range is non blank then the return value will not be one of the prescribed values.

Many thanks

 

[T. Valko]

HI Biff

That would seem to work. The data in the range will be non-numeric. It will probably just be an 'X', to show that that cell/column has been selected.

Also using you formula if more than 1 cell in the range is non blank then the return value will not be one of the prescribed values.

Many thanks

Ok, you said:

So cell range B3:G3 should only have one non blank cell.

So, if more that one cell has an entry what result do you expect?

Book1
	B	C	D	E	F	G
3		X		X
4
5
6
7
8
9
10	9	19	29	39	49	59
Lists

 

[Dermotdjt]

Ideally if more than one cell has an entry then return ERR to show that an ERROR has been been in selecting cells.

 

[T. Valko]

Ideally if more than one cell has an entry then return ERR to show that an ERROR has been been in selecting cells.

Ok, that's easy enough to handle...

=IF(COUNTA(B3:G3)>1,"ERROR",SUMIF(B3:G3,"<>",B10:G10))

However, that raises another issue...

What if there is no entry in B3:G3 at all? The formula will return 0.

 

[Dermotdjt]

No, a zero value would be fine and may actually be desirable. I'll have to check that with the user. Many thanks for such a prompt response.

 

[T. Valko]

No, a zero value would be fine and may actually be desirable. I'll have to check that with the user. Many thanks for such a prompt response.

You're welcome. Thanks for the feedback!