Nested IF(ISBLANK)

[DouglasWilson]

I need a formula for the following for a cell on Worksheet 2 for data from Worksheet 1:


If E:1 is populated then E:1

If E:1 is blank then D:1

If D:1 is blank then C:1

If C:1 is blank then B:1

If B:1 is blank then A:1

If A:1 is blank then ""


I have had no success and had reached my frustration level. Any help would be greatly appreciated.

 

[rwsrws12]

Brute force, but this works:


=IF(E1<>"",E1,IF(D1<>"",D1,IF(C1<>"",C1,IF(B1<>"",B1,IF(A1<>"",A1,"")))))


Obviously use absolute references as appropriate if you plan to copy this to other cells.

 

[jason]

I believe some of the conditions were incorrect:


i think this is what you're after:

=IF(Sheet1!E1<>"",Sheet1!E1,IF(ISBLANK(Sheet1!E1),Sheet1!D1,IF(ISBLANK(Sheet1!D1),Sheet1!C1,IF(ISBLANK(Sheet1!C1),Sheet1!B1,IF(ISBLANK(Sheet1!B1),Sheet1!A1,"")))))

 

[Hui]

Douglas


Firstly, Welcome to the Chandoo.org Forums


This may be a cleaner solution:

=INDEX(Sheet1!A1:E1,,MAX((Sheet1!A1:E1<>"")*(COLUMN(Sheet1!A1:E1)))) Ctrl+Shift+Enter


or if A1:E1 just contains numbers

=MAX(IF(Sheet1!A1:E1<>"",Sheet1!A1:E1,""))Ctrl+Shift+Enter