Formula for "If blank then A, if not blank then B"

[cvvveu]

I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.

So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.

Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?

 

[shjared]

I need a more complex version:

I have 5 columns of dates. Columns 1-4 get added as time and events happen. When an even occurs, the date goes in column 1. The second time the event occurs the date goes in column 2, etc. I would like column 5 to look at the first 4 columns and reflect the last date given. I'm thinking the logic would be something like: If A4 is blank, than A3. If A3 is blank than A2. If A2 is blank than A1. If A1 is blank than blank. But I have no idea how to write that formula! Any advice?

 

[jtakw]

Hi,

Try this:

Excel 2010
	A	B	C	D	E
1	1/2/2016	1/31/2016	2/16/2016		2/16/2016
Sheet1

Cell Formulas
Range	Formula
E1	=LOOKUP(9.99999999999999E+307,A1:D1)

 

[shjared]

Hi,

Try this:

Excel 2010

	A	B	C	D	E
1	1/2/2016	1/31/2016	2/16/2016		2/16/2016

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula E1 =LOOKUP(9.99999999999999E+307,A1:D1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

That worked perfectly! Thank you!

 

[jtakw]

That worked perfectly! Thank you!

You're welcome, welcome to the forum.

 

[Jackrbbt]

I could use some help with the ISBLANK function. I use excel to track the mandatory training completion of employees in our various departments. I use algorithms that place a "1" in a tally column if the date entered is current or a "0" if no current date is entered. These are then totaled for percent completed by each employee and for each training item. The problem I am having is with one-time training where there isn't an expiration. I want it to enter a "0" in E3 if there is no date entered in E4 and a "1" if there is. Thanks in advance for any help.

 

[Eric W]

Welcome to the forum.

For future reference, it's usually better to open a new thread for a question, instead of adding your question to an old thread. It's often a matter of luck if anyone will see your question.

As far as your question, how about:

E3: =IF(E4="",0,1)

There are some differences between ISBLANK and ="", mainly how they handle a cell with a formula that returns a blank.

 

[Jackrbbt]

Welcome to the forum.

For future reference, it's usually better to open a new thread for a question, instead of adding your question to an old thread. It's often a matter of luck if anyone will see your question.

As far as your question, how about:

E3: =IF(E4="",0,1)

There are some differences between ISBLANK and ="", mainly how they handle a cell with a formula that returns a blank.

Thanks Eric. Much appreciated. - Ken

 

[Excel2010]

This is great! Can I add one twist? Can I get the same calculation only using working days? I wasn't sure how to incorporate NETWORKDAYS into the formula

Try

=A1-IF(B1="",TODAY(),B1)