Excel 2010 - Icon Set (3 conditions) Based on Cell Value Across

 
Hi,

I have uploaded a sample Excel 2010 file with example problem I am having trying to implement Icon Sets.
The spreadsheet contains an “Estimate” Column (B) which is supposed to show a red, yellow, or green icon based the cell value across from it in the “Labor Hours Total” Column (A).

I believe the setup conditional formula should show the prefer icon based on how it’s setup which is:

1. Red: “Estimate” Hours is greater than the “Labor Hours Total”
2. Green: “Estimate” Hours is greater than 0 or Equal to “Labor Hours Total”
3. Yellow: “Estimate” Hours is 0 or less than “Labor Hours Total”

However, it appears that the only value being referenced in the conditional format is cell A2 which is referred to as $A$2 in the conditional format.
Can anyone help suggest what I need to do above? I need the Estimate value which is a calculated field to show at all times since it is consistently being referred to so I can replace it with a 1,-1, 0 value.

I am really hoping someone help and I’m sorry if this seems like a repost. I just couldn't seem to find a similar problem in the forums.

Get rid of the dollar sign in front of the two.

$A2
[Edit]

Scratch that. I forgot icon sets prevent relative referencing. Hold while I figure this out.

Thanks for looking into this!

By the way I meant to state earlier that the Estimate column must remain a calculated sum value and I can not convert it to a 1, -1, 0 value. I don't think this makes a difference to the solution however, so not sure how important it is.

You can create a helper column between A and B. In the NEW B column, enter this formula:

Please Login or Register  to view this content.

Add an Icon set in this new column to be Green if >= 1, Red if >= 0, and Yellow is less than 0. Select the "Show Icon Only" option.

Remove the Icon sets from the OLD B (now C) column.

This works great - thank you for the help!

In order to economize, I was hoping to keep the icon set in the same cell as the estimate, however, there just doesn't seem to be anyway to do it.

In any case, I'm marking this as solved.

Robynn

The only way I can think of is to use VBA to create a custom function.