Conditional Formatting with icon sets and relative referencing in excel

Question

I was wondering if there is any way to use relative referencing with icon sets in excel. Ideally I would like to lock the row whilst allowing the column to change as the column is copied and pasted across the worksheet (ex. D$3). Excel is telling me that I'm not able to use relative referencing with icon sets.

for clarification, what I'm trying to do is apply an icon set based on the relation between the current day and the target date of a project to be completed.

-The cell will show a checkmark so long as there is at least a week before the target date or if the cell reads 100%.

-If there are less than 7 days between the target date and current date then it will show an exclamation mark.

-otherwise show an x when the current day is the same as the target date and the cell's value is not 100%

the code that is being used as a condition is:

=OR(TODAY()+7-$D$14,$D$12=100)

What I would like is something equivalent to:

=OR(TODAY()+7-D$14,D$12=100)

I just don't know how to go about doing that thanks in advance

below is the formatting rule window:

Solution

Without % appearing in all cells the only way I can see to meet your requirement is to enter 100 in those cells to display tick and 100% and then format those specific cells #,###"%".

Comments

[impactblu]

it says I can't use relative referencing so even if I take out the $ it'll toss me an error and won't proceed with the okay

[PowerUser]

I see what you're saying. I wouldn't have expected that limitation.

[impactblu]

yea it caught me offguard seeing as the "Use a formula to determine which cells to format" rule allows relative referencing

[impactblu]

I don't fully understand what you're trying to say here. Could you elaborate?

[impactblu]

I understand what you're trying to say and I figured out that I had to use 1 instead of 100 earlier I just never updated that code block however, this isn't really addressing the issue of the relative references. My current idea is to write a macro that will manually change the address value.

[impactblu]

I'm not sure how I'm going to address this. It arose from switching the colored cells to icon sets to represent project progress because it was easier to read from the printer. Now it's just become a thorn in my side.