CODEWITHSUNDEEP
excelexcel-formula
jnmf
jnmf

Reputation: 137

Conditional formatting depending on data from a column

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?

Upvotes: 0

Views: 71

Answers (2)

ashleedawg
ashleedawg

Reputation: 21619

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

example3

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:

screenshot example

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

ex1 ex2

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

hide formula bar

Upvotes: 0

pnuts
pnuts

Reputation: 59442

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.

Upvotes: 1

Related Questions