We help IT Professionals succeed at work.

Excel 2007: Conditional format with text input

JCJG
JCJG asked
on
Hi, is it possible to format a cell based on the value of another cell and insert text if the conditional is met?  For example, if value of cell B1 is greater than 100, cell C1 will be highlighted yellow and with "Please explain" inserted.  Thanks.
Comment
Watch Question

Yes, you can do it. See attached file.

Sincerely,
Ed
Formatting.xlsx
Top Expert 2008

Commented:
Do you have anything currently in cell C1 ?

If not, you can put a formula like =B1>100, use a custom format like "Please Explain";;
and add the conditional format on top of that for the color

Thomas

Author

Commented:
I apologize for not making myself clear.  I would like to do it without putting a formula in the cell.  In other words, if B1 is >100, the words "Please Explain" will be in the cell, otherwise the cell would be blank?  Is it doable without VB?
Top Expert 2008

Commented:
Not possible without VB as far as I know

T
Most Valuable Expert 2011
Top Expert 2011

Commented:
Not with the cell blank, no. If you have something in there, even if just a space, then yes, but otherwise you would need VB as Thomas said.
I guess my first question would be why does C1 need to be blank if you want it to appear to have text in?

Author

Commented:
I guess it doesn't have to be blank but I felt it would look cleaner. The text "Please Explain" seems intuitive for user to overwrite with comment.  I prefer to see only cells that required comments with text and those that doesn't blank or space when I move down a column.
Most Valuable Expert 2011
Top Expert 2011

Commented:
But the text would only appear for those cells where applicable. The rest of the cells would appear empty - that was the point of Thomas' suggestion.
- Add formula =IF(B1>100,"Please explain","") to C1 and copy down (if needed)
- Add Conditional Formatting to column C with a Greater than "" condition (greater than blank so that explanations will remain highlighted for the reviewer)
- Select all cells
- Right-click > Format Cells > Protection > clear Locked checkbox
- Select Column C
- Right-click > Format Cells > Protection > click Hidden
- Select Review tab > Protect Sheet > by default, boxes should be checked for:
     - Protect worksheet and contents of locked cells
     - Select locked cells
     - Select unlocked cells
- Optionally, set a password

This will get you the behavior you want while also hiding the formula from the end-user.  See attached... Formatting.xlsx
Rob HensonFinance Analyst
Commented:
I have come up with a possible way round it.

Assuming value in column B, put formula as suggested above in column C:

=IF(B1>100,"Please explain.","")

Make column C as narrow as possible without being hidden. The narrowest I could get was 0.08

The text will then overspill into column D. Hiding column C will make text disappear.

Apply conditional formatting to column D using one of two conditions:
value of column C <> ""
or column B > 100

Expand column D so that the text appears to be within the formatted cell.

This then allows for the comment cell to start blank so no worries about a formula being overwritten by accident, gives highlighting and text message for lines that need explanation.

See attached.

Thanks
Rob H

Conditional-formatting-on-blank-.xls
Rob HensonFinance Analyst

Commented:
Sorry, I missed out a line of explanation.

When comment is added in column D, the "Please explain" disappears because column C cannot overflow into the column D cell. When comment is deleted the "Please explain" re-appears.

Thanks
Rob H
Rob - Not a bad alternative but a couple of potential issues:

users will see the formula if they're tabbing across from B to D
there may be questions about the missing column C and the "stutter" that happens when they tab across cells
Rob HensonFinance Analyst

Commented:
If the cells in column C are set to Locked and the sheet is protected with Select Locked Cells unticked as you were suggesting, cells in column C can't be selected and moving right from column B goes straight to column D and vice versa.

Thanks
Rob H
Rob HensonFinance Analyst

Commented:
In addition, row and column headers and gridlines could be hidden so the viewer is none the wiser.

Thanks
Rob H
True enough, but seems like more setup/sacrifice for essentially the same result I proposed.

Another alternative - set up Data Validation on Column B as follows:

- Select Column B
- Select Data tab > Data Validation > Settings tab
   - Allow: Whole Number (or Decimal if appropriate)
   - Data: less than or equal to
   - Maximum: 100
- Error Alert tab
   - Style: Information (or Warning)
   - Title: Explanation Needed (or any other preferred text)
   - Error Message: "Column B values over 100 require an explanation in Column C" (or any other preferred text)

- Select Column C
- Select Conditional Formatting > Highlight Cell Rules > More Rules > Use a formula to determine which cells to format
- Format values where this formula is true: =B1>100

This removes the issue of manipulating the cell widths/protection/etc. while achieving (almost) the same result.  The biggest difference is that the "Please explain" prompt is at time of entry and isn't persistent once they leave the current cell (although the cell highlighting remains).  Another advantage, however, is that it requires a deliberate user action to acknowledge the explanation request.

 Formatting.xlsx

Author

Commented:
Mark and Bob, I appreciate your suggestions and the creativity.  I have read each one carefully and have determined to use a combination of techniques both of you suggested that best fit my situation.  I will use the additional narrow column and lock and hide the column upon protection.  I will allow selection locked cells since there are other cells in the worksheet I want to let user select.

Thanks you very much!