If 3 cells are blank return the vaule of another, or return the value of cell with a vaule

Hi all,

I am after a little help with the below: -


In line 1 I have a static value which is either x or blank (this is determined by a formula). Lines 2,3 and 4 are either blank or have the letters b or l in them (again determined by a formula). I am trying to put together a formula where, if cells 2,3 and 4 are blank the value of line 1 is retrieved however, if lines 2,3 or 4 have a value in them that is retrieved rather than line 1.

I have tried to change b and l to numbers and try a sum at the bottom of all the rows but it is not counting the numbers (potentially as they are from a formula and not typed in).


1 x x x x x x x x x x x x x x x x x x x x
2 0 b b b
3 0 0 0 0 l l l
4 0 0 0 0 0 0 b b b b

I hope this makes sense, unfortunately I am unable to upload an example at the moment - I have tried to show an example above (blank cells on lines 2-4 are shown as zero so I could space them out), any help would be appreciated.

thanks

"if lines 2,3 or 4 have a value in them that is retrieved rather than line 1. "

So if there is a value in lines 2, 3, or 4
ONLY ONE OF THOSE LINES will have a value?

If so, use COUNTA() to determine number of blanks

=IF(COUNTA(A2:A4)=3,A1,IF(A2<>"",A2,IF(A3<>"",A3,A4)))

or

=IF(A2<>"",A2,IF(A3<>"",A3,IF(A4<>"",A4,A1)))

Can you upload a file, even a mockup would suffice.

Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.