ICON Conditional Format based on a different row - or formula , but result not in cell

I'm playing with some icons for conditional formatting

I want to use a Check and a Cross

so I have
column A - this has actuals
column B - this has a goal

in column A - I also want an icon conditional format where the current value and the check/cross is displayed
i don't want to add another column between Column A or B
but can have some helper columns in AA or AB columns etc

a check if the value in A is = or > then the value in B
a cross if the value in A is < then the value in B

can seem to do it

I dont want amber !

happy if a macro is the only way to go
and in the real spreadsheet the range to apply would be
D10 to D53 but may/will extend or contract each week

helper column is BA and will be hidden

i have attached a simple sheet

thanks for any help

One way (probably the only way!) which involves displaying the helper column (can be before column A and not between columns A &B)

http://blog.contextures.com/archives...xcel-icon-set/

sorry not sure how i can use that , in my example

I do not think there is a very good way but perhaps camera tool may work?

Well - thats an incredible tool , really clever , and just applied on the main sheet and it works !!!!

wow

Thanks very much for that

You are welcome.

well you can do it in the cell but as icon sets dont like relative references its a bit of a pain
you need to use the formula from the drop down in icon sets (where it says percentage /number/formula )
then use the formula
like this
=IF($A$2<$B$2,$A$2+1,$A$2-1)
see attached
but you have to redo each one individually

Thanks for that
martindwilson

But how do I apply to the next cell down

I can make it work on one cell in the main sheet
BUT not sure how to copy
or do i need to enter for every cell

I'm applying to a range D10 to D52
and comparing
D10 with F10

D10 needs to be = or > then F10 for a green check mark

if I select all the range D10 - D52 - because of the $$ it does not seem to change the cells - as it has on yours

i said you cantas icon sets have to be absolute references i did them one at a time

I suspected that , just wanted to make sure that was the case

Thanks
i guess I could do 42 rows individually - thanks for the formulas and taking the time to look