conditional formatting, icon sets with formula
Hi,
I always get stuck with icon sets, I am trying to do the following:
if 'Due Date' (A) is greater than 'Achieved' (B) then (B) has 'black circle' and
if 'Due Date' (A) is less than or equal to 'Achieved' (B) then (B) has 'green circle'
A B
15/01/14 02/02/14
thanks
Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Sorry I am unable to upload from my workplace, I have listed the columns above
example.jpg
this is a picture of what I'm working with, as you can see there are some items showing overdue when actually they have been achieved.
Thanks
Sorry, we can't work with pictures
Ok, so maybe someone can tell me if it is possible to get conditional formatting to include a cell in an adjacent column in its calculation.
What I'm working with is no more than what was included in the image two columns of dates, one with a due date and its neighbour the achieved. I do not want the due date to show red if the adjacent achieved date has been completed
Thanks
yes
a sample spreadsheet - no matter how basic can help - as we can assexamples
whats in the adjacent cell the word "adjacent" ? a date , simply not blank
you can then use
=AND( adjacent_cell <> "", test for dates)7
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi Wayne,
Sorry I am unable to upload documents due to computer restrictions. the cells are set out as follows:
Due Date Achieved Date
15/01/14 02/02/14
03/02/14
18/02/14
18/02/14 04/02/14
the ajacent cell (Achieved date) will either be blank or contain a date.
Conditional formatting with icon sets has proven dificult as it seems to only work with set cells not a set column according to its row,
this is what I am aiming for:
Green - if the ajacent Achieved Date is not blank
Red - if date is in the past and the ajacent Achieved Date cell is blank
Yellow - if date is greater than Today() + $L$$ and the ajacent Achieved Date cell is blank ($L$3 is a cell in which the user can put due days)
i have setup a formula in a cell to create a range of numbers 1,2,3 for the icon set
=IF(C2<>"",3,IF(AND(B2>TODAY()+$L$3,C2=""),2,IF(AND(B2<TODAY(),C2=""),1,"")))
BUT I'm not 100% sure of your rules
If i put 46 into L3
then none of the rules above apply
if date is greater than Today() + $L$$ so that can put the date into the past
today() + 46
will make the example data have a date in the past
your 18/02/2014
for example
and then none of the other rules use the L3 - so it does not work ......
I have attached an example - but i guess you wont be able to download due to restrictions
You can also just display the icon and not the number
Thanks for your responce Wayne,
but is there a way of having the icon set in the due date cell without changing the cells value?
sorry, if that is possible, then i do not know how to do that.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks