Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designing a spreadsheet to track workflow
Hi,
I want to design a spreadsheet which tracks when certain pieces of work are due and whether theyve arrived or not. At the moment there are three cells, all in a row that I want to interact with each other. In the first cell the user will enter the date on which the work is due. In the second cell, I want the user to enter €˜in, when the work is in. If no text is entered is entered into the second cell, I want the third cell to display the word €˜LATE, in red caps. Ive seen something like this done, but I dont know how to do it myself. Can anyone give me an advice or point me to a good tutorial? Thanks Karl |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designing a spreadsheet to track workflow
Two steps required to do this. In the third cell (C1 and assuming 2nd cell
is B1) put this formula: =IF(B1="","LATE","Completed") or substitute another phrase for "Completed" if you like, or even nothing by using ,"") as that last parameter. Then select C1 and use Format | Conditional Formatting and set the condition to: Cell Value Is | equal to and type the word LATE into the 3rd area on that row. Then click the Format... button and choose red font color along with Bold. [OK] to close the format dialog, [OK] again to close the Conditional Formatting dialog. Should work as you desire. "Karl" wrote: Hi, I want to design a spreadsheet which tracks when certain pieces of work are due and whether theyve arrived or not. At the moment there are three cells, all in a row that I want to interact with each other. In the first cell the user will enter the date on which the work is due. In the second cell, I want the user to enter €˜in, when the work is in. If no text is entered is entered into the second cell, I want the third cell to display the word €˜LATE, in red caps. Ive seen something like this done, but I dont know how to do it myself. Can anyone give me an advice or point me to a good tutorial? Thanks Karl |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designing a spreadsheet to track workflow
Thank you very much, much appreciated.
"JLatham" wrote: Two steps required to do this. In the third cell (C1 and assuming 2nd cell is B1) put this formula: =IF(B1="","LATE","Completed") or substitute another phrase for "Completed" if you like, or even nothing by using ,"") as that last parameter. Then select C1 and use Format | Conditional Formatting and set the condition to: Cell Value Is | equal to and type the word LATE into the 3rd area on that row. Then click the Format... button and choose red font color along with Bold. [OK] to close the format dialog, [OK] again to close the Conditional Formatting dialog. Should work as you desire. "Karl" wrote: Hi, I want to design a spreadsheet which tracks when certain pieces of work are due and whether theyve arrived or not. At the moment there are three cells, all in a row that I want to interact with each other. In the first cell the user will enter the date on which the work is due. In the second cell, I want the user to enter €˜in, when the work is in. If no text is entered is entered into the second cell, I want the third cell to display the word €˜LATE, in red caps. Ive seen something like this done, but I dont know how to do it myself. Can anyone give me an advice or point me to a good tutorial? Thanks Karl |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designing a spreadsheet to track workflow
One more question
If I wanted C1 to display "LATE" if the deadline date in A1 had passed, but not to display anything (or to display "completed") if the user had written "IN" in B1, would that be possible? Thanks Karl "JLatham" wrote: Two steps required to do this. In the third cell (C1 and assuming 2nd cell is B1) put this formula: =IF(B1="","LATE","Completed") or substitute another phrase for "Completed" if you like, or even nothing by using ,"") as that last parameter. Then select C1 and use Format | Conditional Formatting and set the condition to: Cell Value Is | equal to and type the word LATE into the 3rd area on that row. Then click the Format... button and choose red font color along with Bold. [OK] to close the format dialog, [OK] again to close the Conditional Formatting dialog. Should work as you desire. "Karl" wrote: Hi, I want to design a spreadsheet which tracks when certain pieces of work are due and whether theyve arrived or not. At the moment there are three cells, all in a row that I want to interact with each other. In the first cell the user will enter the date on which the work is due. In the second cell, I want the user to enter €˜in, when the work is in. If no text is entered is entered into the second cell, I want the third cell to display the word €˜LATE, in red caps. Ive seen something like this done, but I dont know how to do it myself. Can anyone give me an advice or point me to a good tutorial? Thanks Karl |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Designing a spreadsheet to track workflow
Replace the formula in C1 with this:
=IF(B1="",IF(NOW()A1,"LATE","Due"),"Done") or if you want to force entry of "IN" (or "in" or "In") into B1 and not accept anything else like an 'x' or 'OK", do it this way: =IF(B1<"IN",IF(NOW()A1,"LATE","Due"),"Done") "Karl" wrote: One more question If I wanted C1 to display "LATE" if the deadline date in A1 had passed, but not to display anything (or to display "completed") if the user had written "IN" in B1, would that be possible? Thanks Karl "JLatham" wrote: Two steps required to do this. In the third cell (C1 and assuming 2nd cell is B1) put this formula: =IF(B1="","LATE","Completed") or substitute another phrase for "Completed" if you like, or even nothing by using ,"") as that last parameter. Then select C1 and use Format | Conditional Formatting and set the condition to: Cell Value Is | equal to and type the word LATE into the 3rd area on that row. Then click the Format... button and choose red font color along with Bold. [OK] to close the format dialog, [OK] again to close the Conditional Formatting dialog. Should work as you desire. "Karl" wrote: Hi, I want to design a spreadsheet which tracks when certain pieces of work are due and whether theyve arrived or not. At the moment there are three cells, all in a row that I want to interact with each other. In the first cell the user will enter the date on which the work is due. In the second cell, I want the user to enter €˜in, when the work is in. If no text is entered is entered into the second cell, I want the third cell to display the word €˜LATE, in red caps. Ive seen something like this done, but I dont know how to do it myself. Can anyone give me an advice or point me to a good tutorial? Thanks Karl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet shortcuts not working | Setting up and Configuration of Excel | |||
Spreadsheet Security | Excel Discussion (Misc queries) | |||
CountIF Function On Linked Spreadsheet | Excel Discussion (Misc queries) | |||
Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
Spreadsheet merging problems | Excel Worksheet Functions |