Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
I posted a question a few weeks ago about a spreadsheet of dates I have. I
have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
A quick check reveals that the $o223 reference has a Zero not the letter o
apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
thanks for that dazzadata. I've amended it but it's still not working
properly. It's putting dates like 28th feb 07 in red and dates in april in amber?!!! should just be 1 week away from todays date in red and 14 days in amber. "DazzaData" wrote: A quick check reveals that the $o223 reference has a Zero not the letter o apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Hmm,
Are you sure that the formulae are looking at the right values i.e. value of the cell it is in otherwise the shading may be out of step with the date. The formula itself is fine so positioning might be the issue "singingsister" wrote: thanks for that dazzadata. I've amended it but it's still not working properly. It's putting dates like 28th feb 07 in red and dates in april in amber?!!! should just be 1 week away from todays date in red and 14 days in amber. "DazzaData" wrote: A quick check reveals that the $o223 reference has a Zero not the letter o apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
It needs to look from K3, L3 and M3 downwards with no end row as it's a
working spreadsheet. Does this help? "DazzaData" wrote: Hmm, Are you sure that the formulae are looking at the right values i.e. value of the cell it is in otherwise the shading may be out of step with the date. The formula itself is fine so positioning might be the issue "singingsister" wrote: thanks for that dazzadata. I've amended it but it's still not working properly. It's putting dates like 28th feb 07 in red and dates in april in amber?!!! should just be 1 week away from todays date in red and 14 days in amber. "DazzaData" wrote: A quick check reveals that the $o223 reference has a Zero not the letter o apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
I would check the conditional formulae in k3,l3,m3 and check they are
looking at k3,l3,m3 & $O3, it may be that the rows in the conditional formulae are out of step with the rows in the spreadsheet which would lead to seemingly peculiar shading decisions e.g. if the conditional was looking at row 4 in Row 3 it would shade row 3 according to the dates in row 4 and appear odd! Failing that start again by pasting in the formula adjusting it from row 223 to row 3, fix the "zero" v "O" and it should work fine "singingsister" wrote: It needs to look from K3, L3 and M3 downwards with no end row as it's a working spreadsheet. Does this help? "DazzaData" wrote: Hmm, Are you sure that the formulae are looking at the right values i.e. value of the cell it is in otherwise the shading may be out of step with the date. The formula itself is fine so positioning might be the issue "singingsister" wrote: thanks for that dazzadata. I've amended it but it's still not working properly. It's putting dates like 28th feb 07 in red and dates in april in amber?!!! should just be 1 week away from todays date in red and 14 days in amber. "DazzaData" wrote: A quick check reveals that the $o223 reference has a Zero not the letter o apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
Please don't multipost. Cross-post if you must.
http://www.cs.tut.fi/~jkorpela/usenet/xpost.html -- David Biddulph "singingsister" wrote in message ... I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
I hate to tell you but I re-entered the formula from scratch and it still
didn't work! "DazzaData" wrote: I would check the conditional formulae in k3,l3,m3 and check they are looking at k3,l3,m3 & $O3, it may be that the rows in the conditional formulae are out of step with the rows in the spreadsheet which would lead to seemingly peculiar shading decisions e.g. if the conditional was looking at row 4 in Row 3 it would shade row 3 according to the dates in row 4 and appear odd! Failing that start again by pasting in the formula adjusting it from row 223 to row 3, fix the "zero" v "O" and it should work fine "singingsister" wrote: It needs to look from K3, L3 and M3 downwards with no end row as it's a working spreadsheet. Does this help? "DazzaData" wrote: Hmm, Are you sure that the formulae are looking at the right values i.e. value of the cell it is in otherwise the shading may be out of step with the date. The formula itself is fine so positioning might be the issue "singingsister" wrote: thanks for that dazzadata. I've amended it but it's still not working properly. It's putting dates like 28th feb 07 in red and dates in april in amber?!!! should just be 1 week away from todays date in red and 14 days in amber. "DazzaData" wrote: A quick check reveals that the $o223 reference has a Zero not the letter o apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
i have
=AND(K30,K3=TODAY(),K3-TODAY()<=5,$O3="") Format Red =AND(K30,K3=TODAY(),K3-TODAY()<=14,$O3="") Format Amber For 30 May - Jun05 inclusive in K3 this shades K3 red For 06 Jun - Jun13 inclusive in K3 this shades K3 amber Am at a loss to explain why it doesn't work for you. Its not putting quotes around it is it? "singingsister" wrote: I hate to tell you but I re-entered the formula from scratch and it still didn't work! "DazzaData" wrote: I would check the conditional formulae in k3,l3,m3 and check they are looking at k3,l3,m3 & $O3, it may be that the rows in the conditional formulae are out of step with the rows in the spreadsheet which would lead to seemingly peculiar shading decisions e.g. if the conditional was looking at row 4 in Row 3 it would shade row 3 according to the dates in row 4 and appear odd! Failing that start again by pasting in the formula adjusting it from row 223 to row 3, fix the "zero" v "O" and it should work fine "singingsister" wrote: It needs to look from K3, L3 and M3 downwards with no end row as it's a working spreadsheet. Does this help? "DazzaData" wrote: Hmm, Are you sure that the formulae are looking at the right values i.e. value of the cell it is in otherwise the shading may be out of step with the date. The formula itself is fine so positioning might be the issue "singingsister" wrote: thanks for that dazzadata. I've amended it but it's still not working properly. It's putting dates like 28th feb 07 in red and dates in april in amber?!!! should just be 1 week away from todays date in red and 14 days in amber. "DazzaData" wrote: A quick check reveals that the $o223 reference has a Zero not the letter o apart from that I got it working fine "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
The equation looks OK
in a cell try =k223-today() Are you getting what you expect? try changing the date in k223 are you sure you are in row 223 for this conditional equation? (I lost almost a full day one time because I was one row off in my conditional references, and I did not check the obvious, since I "wouldn't" make that kind of mistake)) If the conditional formating does not have unwanted absolute or relative references, incorrect references or unwanted Quote marks, it has to be either a date formatting problem or an error in the way your Excel file is handling dates. If everything looks OK and the equation works, close out of excel reopen and try again on a clean excel sheet and then the one with which you are having problemns. If it does not work on the new sheet, borrow someone's computer and enter the format equation in an new excel file and see what happens. "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
OK - the spreadsheet has now been changed majorly by a manager so the columns
are not the same anymore. Please can someone tell me what the formula is from scratch please. The three date columns are now N,O and P rather than K,L and M. "bj" wrote: The equation looks OK in a cell try =k223-today() Are you getting what you expect? try changing the date in k223 are you sure you are in row 223 for this conditional equation? (I lost almost a full day one time because I was one row off in my conditional references, and I did not check the obvious, since I "wouldn't" make that kind of mistake)) If the conditional formating does not have unwanted absolute or relative references, incorrect references or unwanted Quote marks, it has to be either a date formatting problem or an error in the way your Excel file is handling dates. If everything looks OK and the equation works, close out of excel reopen and try again on a clean excel sheet and then the one with which you are having problemns. If it does not work on the new sheet, borrow someone's computer and enter the format equation in an new excel file and see what happens. "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting
=and(N2230,N223=today(),N223-today()<=5,$[new column]223="") change the N to O and P for the other two columns and replace the [new column] with whatever column the dispatch date is in (R?) "singingsister" wrote: OK - the spreadsheet has now been changed majorly by a manager so the columns are not the same anymore. Please can someone tell me what the formula is from scratch please. The three date columns are now N,O and P rather than K,L and M. "bj" wrote: The equation looks OK in a cell try =k223-today() Are you getting what you expect? try changing the date in k223 are you sure you are in row 223 for this conditional equation? (I lost almost a full day one time because I was one row off in my conditional references, and I did not check the obvious, since I "wouldn't" make that kind of mistake)) If the conditional formating does not have unwanted absolute or relative references, incorrect references or unwanted Quote marks, it has to be either a date formatting problem or an error in the way your Excel file is handling dates. If everything looks OK and the equation works, close out of excel reopen and try again on a clean excel sheet and then the one with which you are having problemns. If it does not work on the new sheet, borrow someone's computer and enter the format equation in an new excel file and see what happens. "singingsister" wrote: I posted a question a few weeks ago about a spreadsheet of dates I have. I have tried to do the formula on the actual spreadsheet (did "testing" on dummy sheet before I was certain it worked and got approval from boss to use), but, now I've tried to do the formula again and it doesn't work! To cut a long story short, my spreadsheet has 3 columns I would like to track. Column K is the requested delivery date from the customer, column L is the quoted delivery date and column M is the factory acknowledged delivery date. What I would like to happen, is that if the date in either of the columns reaches between 14 days and 7 days away from todays date it flags yellow (as a warning) and when the date reaches 7 days or less away from todays date the colour changes to red (as a visual warning to chase delivery). What I also would like to do is that when I put a date in column O (which is the actual despatch date) the colours in that row for the 3 columns above disappear. The spreadsheet is a working document so I will need to apply the formula to all rows as it gets added to on a daily basis. The formula I was using was to flag red was =and(k2230,k223=today(),k223-today()<=5,$0223="") and to flag amber i used =and(k2230,k223=today(),k223-today()<=14,$0223="") PLEASE can someone help me out of my pickle. The formula did work?! Promise! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting on =max() | New Users to Excel | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |