Conditional Formatting Icons

[cmf735]

Hi, I'm trying to add conditional formatting icons to the 'Completed' column. I'm unable to make it work using a formal or numbers. I don't know if it's because the 'Completed' column is filled by a formula. Is anyone familiar with this. I'd like a check mark for finished, yellow for started and x for waiting. Thanks.

 

[GraH - Guido]

Hi,
Your formula like =IF(H7="Finished";"100";IF(H7="Started";"60";IF(H7="Waiting";"0";"Failed")))
never returns numbers, but text. "100" = text, due to double quotes.

change it to =IF(H7="Finished";100;IF(H7="Started";60;IF(H7="Waiting";0;"Failed")))
and
update your icon set (rule) to


will show this.


Would that be what you need?

 

[cmf735]

That works perfect. Thank you! I couldn't figure out why it wasn't matching the info. It never crossed my mind to remove the quotations around the numbers.

 

[cmf735]

Hi,
Your formula like =IF(H7="Finished";"100";IF(H7="Started";"60";IF(H7="Waiting";"0";"Failed")))
never returns numbers, but text. "100" = text, due to double quotes.

change it to =IF(H7="Finished";100;IF(H7="Started";60;IF(H7="Waiting";0;"Failed")))
and
update your icon set (rule) to
View attachment 77609

will show this.
View attachment 77608

Would that be what you need?

Let me ask you a question. Do you know how to format the blank cells? So under Priority, Due Date, What etc. when I enter in the date or what I'm working on and I finish and delete the contents of the cell is there a way to make it so those words repopulate a blank cell?

 

[GraH - Guido]

Sorry, can't follow what your are trying to explain here. Please five an example in the file or add a few screenshots.

 

[cmf735]

Sorry, can't follow what your are trying to explain here. Please five an example in the file or add a few screenshots.

Ok let's focus on the "What" column. So see the top cell? It's blank, the rest say "What". They're all entered manually. I tried to format the cell so when it's blank it will say "what" but right now that's not happening. I want the cells to autofill when they're blank. So say I typed "delivery by Friday" in the cell. I complete the task. Now I clear out the cell so it's blank again. I want the blank cell to fill with the word "what" without doing it manually. Does that make sense or am I being to wordy?

 

[cmf735]

Ok let's focus on the "What" column. So see the top cell? It's blank, the rest say "What". They're all entered manually. I tried to format the cell so when it's blank it will say "what" but right now that's not happening. I want the cells to autofill when they're blank. So say I typed "delivery by Friday" in the cell. I complete the task. Now I clear out the cell so it's blank again. I want the blank cell to fill with the word "what" without doing it manually. Does that make sense or am I being to wordy?

Sorry I forgot to attach the file.

 

[p45cal]

I want the blank cell to fill with the word "what" without doing it manually.

Difficult, may be impossible without VBA.
However, depending on the purpose there could be other solutions; what is the reason you want those words to appear in that column when the cell is blank? I'm guessing it might be to prompt the user and tell him the kind of data that's required in that blank cell, especially if the user has scrolled down the table and the table headers are no longer visible. If that's the case there are a couple of things you might try.
1. Make the table into a proper Excel table. When this is done, if the user scrolls down the table when the headers disappear off the top, the headers appear instead where the column letters were.
2. Another approach might be to use Data Validation, but only the Input Message tab. A small pop up appears when the cell is selected.

I've done both of the above in the attached.

Maybe I've missed the point.

 

[cmf735]

Difficult, may be impossible without VBA.
However, depending on the purpose there could be other solutions; what is the reason you want those words to appear in that column when the cell is blank? I'm guessing it might be to prompt the user and tell him the kind of data that's required in that blank cell, especially if the user has scrolled down the table and the table headers are no longer visible. If that's the case there are a couple of things you might try.
1. Make the table into a proper Excel table. When this is done, if the user scrolls down the table when the headers disappear off the top, the headers appear instead where the column letters were.
2. Another approach might be to use Data Validation, but only the Input Message tab. A small pop up appears when the cell is selected.

I've done both of the above in the attached.

Maybe I've missed the point.

I made a Word document where I was able to have the blank cells autofill. I thought there might be a way to do it with Excel. The only thing I've been able to is fill it with a color would like it to fill with the words so as the list grows the blank cells autofill so I don't have to turn it into an actual table and freeze the top row.

 

[p45cal]

so I don't have to turn it into an actual table and freeze the top row.

If it's a proper Excel Table you won't need to freeze the top row; as long as the selection is somewhere within the table you will always be able to see the headers. (See point 1 I mentioned in msg#8)

But again:

what is the reason you want those words to appear in that column when the cell is blank?

 

[cmf735]

If it's a proper Excel Table you won't need to freeze the top row; as long as the selection is somewhere within the table you will always be able to see the headers. (See point 1 I mentioned in msg#8)

But again:

It's something I like design-wise. I'm a little OCD about it. I like consistency. I can do it in Word with content control I was hoping I could do it in Excel. I thought if I format the cell when it's blank to autofill it would work but that isn't. I tried using the developer tab but I'm not getting any luck. If I can't do it I'll just have to move on.

 

[p45cal]

I tried using the developer tab but I'm not getting any luck. If I can't do it I'll just have to move on.

The only way to get exactly what you want is (through the Developer tab if you will, but we'll do it without) to write a macro (=vba) to do it.
In the file you atached to msg#7, right-click on the tab Sheet1 and choose View code. The VB Editor should show up and the blinking cursor is where you paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errorexit
StringsInColumns = Array("", "", "", "Priority", "Due Date", "What", "Who", "Status")
Set CellsToCheck = Range("D6:H20").SpecialCells(xlCellTypeBlanks)
If Not CellsToCheck Is Nothing Then
  Application.EnableEvents = False
  For Each cll In CellsToCheck.Cells
    cll.Value = StringsInColumns(cll.Column - 1)
  Next cll
End If
errorexit:
  Application.EnableEvents = True
End Sub

Then go back to your sheet and adjust something in the table.
If it works to your satisfaction, save the file as .xlsm or .xlsb

 

[cmf735]

The only way to get exactly what you want is (through the Developer tab if you will, but we'll do it without) to write a macro (=vba) to do it.
In the file you atached to msg#7, right-click on the tab Sheet1 and choose View code. The VB Editor should show up and the blinking cursor is where you paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errorexit
StringsInColumns = Array("", "", "", "Priority", "Due Date", "What", "Who", "Status")
Set CellsToCheck = Range("D6:H20").SpecialCells(xlCellTypeBlanks)
If Not CellsToCheck Is Nothing Then
  Application.EnableEvents = False
  For Each cll In CellsToCheck.Cells
    cll.Value = StringsInColumns(cll.Column - 1)
  Next cll
End If
errorexit:
  Application.EnableEvents = True
End Sub

Then go back to your sheet and adjust something in the table.
If it works to your satisfaction, save the file as .xlsm or .xlsb

That worked! That is amazing! How did you do that?