Conditional formatting depending on data from a column

Question

I'd like to create a rule that writes "100%" in column A whenever I write "end" in column B. So it would be something like this:

[]     X
100%   end
[]     Y

The brackets represent an empty cell.

I've tried:

IF(B1:B3="end",100%,"")

but I would like to leave the cells on column A empty, with no data or formulas, if the respective cell in column B doesn't contain "end".

So I've thought about adding a new rule with conditional formatting but it's not working. Any solutions?

Answer 1

Can be achieved with CF but rather nastily. You would need to fill ColumnA first, say with 1s - but can be hidden by (standard) formatting the text to match the background colour (ie usually white).

Then clear any CF from and select ColumnA, HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=B1="end"  

Format..., select Black (probably) font and Custom Number format of:

00%

OK, OK.

If your blank cells must remain blank in reality rather than appearance then CF alone is not a solution for you.

Answer 2

Edit:

You can get a value to show in a cell based on the value of another cell, without having anything* in the cell, and without VBA:

^_*kinda

Using cells I2:J9 as an example, the steps are:

1. Click I2, then Ctrl+1 to open the Format Cells dialog.

2. On the Number tab, click Custom, then in the Type box enter: ;;; and click _^OK.

3. Hit Alt+O+D+N to add new Conditional Format and choose Use a formula to determine which cells to format.

4. In the Format values where... box, enter: =J2="end" (If Excel adds $'s, remove them), then click Format...

5. On the Number tab, click Custom, then in the Type box enter: ;;;"100%" . (Optionally set a color, font, etc.) then hit _^ENTER 3 times to return to the worksheet.

6. Click I2, hit your _^SPACEBAR once and then hit _^ENTER. ^{(* Fine, I confess: the cell isn't totally empty.)}

7. Click I2 and hit Ctrl+C to Copy.

8. Select I2:J9 and press Alt+E+S+T then hit _^ENTER to Paste Formats.

Now when you type end into any of J2:J9, the corresponding cell in column I will display 100%.

You can also repeat this process to add additional conditions to the same cells if needed.

---

Original Answer: _{^{(Alternative Solutions)}}

^{Sometimes I get stuck spending too much time on a question about a very minor issue. This is one of those times.} :)

Thinking further about your question, I suspect you might not have meant you need the "cells on column A empty, with no data or formulas", but perhaps you mean you want the cells to "appear empty".

---

Example:

These cells do appear empty, unless you to click on the cell and then look at the formula bar:

...but the formula bar can be hidden too: