Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
Greetings,
We have excel sheets created everday for some tasks. There are 2 columns that are getting filled manually. ColumnA: Filled with either SUCCESS, FAIL or OTHER ColumnB: If the columnA is filled with either FAIL or OTHER, then we are filling column B with some reason for these values. So in short, if ColumnA's value is either FAIL or OTHER, then ColumnB should NOT BE NULL. I am looking for a script in excel (macro or something) or a report that should tell me records(may be in a mail or any text file...) which have FAIL or OTHER in column A but column B is empty and the no of records matching this criteria. Is there a way to achieve this using some code in excel? Also, I have to make sure that ColumnA should not be having any values other than SUCCESS, FAIL or OTHER. For now, we are doing this manually. But we have lot of excel files and each one has pretty big data. I want to achieve this using a code in excel to avoid manual work. Can anyone please advise. TIA |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
A simple way is to use an adjacent col to monitor the data-entry in cols A
and B, and provide the necessary visual alert Assuming data entry in row2 down In C2: =IF(AND(OR(A2={"Fail","Other"}),B2=""),"< Fill-in reason","") Copy down as far as required. Format col C in red/bold font, whatever, to make the visual alert outstanding. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "test" wrote in message ... Greetings, We have excel sheets created everday for some tasks. There are 2 columns that are getting filled manually. ColumnA: Filled with either SUCCESS, FAIL or OTHER ColumnB: If the columnA is filled with either FAIL or OTHER, then we are filling column B with some reason for these values. So in short, if ColumnA's value is either FAIL or OTHER, then ColumnB should NOT BE NULL. I am looking for a script in excel (macro or something) or a report that should tell me records(may be in a mail or any text file...) which have FAIL or OTHER in column A but column B is empty and the no of records matching this criteria. Is there a way to achieve this using some code in excel? Also, I have to make sure that ColumnA should not be having any values other than SUCCESS, FAIL or OTHER. For now, we are doing this manually. But we have lot of excel files and each one has pretty big data. I want to achieve this using a code in excel to avoid manual work. Can anyone please advise. TIA |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
Hi,
You could also set up conditional formatting in column B: To conditionally format your cell(s): In 2003: 1. Select the cells you want to format (here I am assuming B1:B10) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =AND(OR(A1="Fail",A1="Other"),B1="") 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted (column B for you) 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =AND(OR(A1="Fail",A1="Other"),B1="") 5. Click the Format button and choose a format. 6. Click OK twice You can also write a formula which tells you how many are missing their entry: in 2003: =SUMPRODUCT(((A1:A10="Other")+(A1:A10="Fail"))*(B1 :B10="")) in 2007: =SUM(COUNTIFS(A1:A10,{"Fail","Other"},B1:B10,"")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "test" wrote: Greetings, We have excel sheets created everday for some tasks. There are 2 columns that are getting filled manually. ColumnA: Filled with either SUCCESS, FAIL or OTHER ColumnB: If the columnA is filled with either FAIL or OTHER, then we are filling column B with some reason for these values. So in short, if ColumnA's value is either FAIL or OTHER, then ColumnB should NOT BE NULL. I am looking for a script in excel (macro or something) or a report that should tell me records(may be in a mail or any text file...) which have FAIL or OTHER in column A but column B is empty and the no of records matching this criteria. Is there a way to achieve this using some code in excel? Also, I have to make sure that ColumnA should not be having any values other than SUCCESS, FAIL or OTHER. For now, we are doing this manually. But we have lot of excel files and each one has pretty big data. I want to achieve this using a code in excel to avoid manual work. Can anyone please advise. TIA |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
On Feb 21, 11:24*pm, Shane Devenshire
wrote: *Hi, You could also set up conditional formatting in column B: To conditionally format your cell(s): In 2003: 1. Select the cells you want to format *(here I am assuming B1:B10) 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =AND(OR(A1="Fail",A1="Other"),B1="") 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted (column B for you) 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =AND(OR(A1="Fail",A1="Other"),B1="") 5. Click the Format button and choose a format. 6. Click OK twice You can also write a formula which tells you how many are missing their entry: in 2003: =SUMPRODUCT(((A1:A10="Other")+(A1:A10="Fail"))*(B1 :B10="")) in 2007: =SUM(COUNTIFS(A1:A10,{"Fail","Other"},B1:B10,"")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "test" wrote: Greetings, We have excel sheets created everday for some tasks. There are 2 columns that are getting filled manually. ColumnA: Filled with either SUCCESS, FAIL or OTHER ColumnB: If the columnA is filled with either FAIL or OTHER, then we are filling column B with some reason for these values. So in short, if ColumnA's value is either FAIL or OTHER, then ColumnB should NOT BE NULL. I am looking for a script in excel (macro or something) or a report that should tell me records(may be in a mail or any text file...) which have FAIL or OTHER in column A but column B is empty and the no of records matching this criteria. Is there a way to achieve this using some code in excel? Also, I have to make sure that ColumnA should not be having any values other than SUCCESS, FAIL or OTHER. For now, we are doing this manually. But we have lot of excel files and each one has pretty big data. I want to achieve this using a code in excel to avoid manual work. Can anyone please advise. TIA- Hide quoted text - - Show quoted text - Thanks Max/Shane. That works well. Also, is it possible to have user know about any pending values he needs to fill when he closes the excel sheet. I mean when he tries to close the excel, a pop up that shows if any column (with values as FAIL or OTHER) is not filled with any reasons. TIA |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
Welcome. Try a new post in .programming for your new query.
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "test" wrote Thanks Max/Shane. That works well. Also, is it possible to have user know about any pending values he needs to fill when he closes the excel sheet. I mean when he tries to close the excel, a pop up that shows if any column (with values as FAIL or OTHER) is not filled with any reasons. TIA |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
On Feb 24, 3:37*am, "Max" wrote:
Welcome. Try a new post in .programming for your new query. -- Max Singaporehttp://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "test" wrote Thanks Max/Shane. That works well. Also, is it possible to have user know about any pending values he needs to fill when he closes theexcelsheet. I mean when he tries to close theexcel, a pop up that shows if any column (with values as FAIL or OTHER) is not filled with any reasons. TIA Thanks Max. I have done that. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auditing excel for everyday tasks
Thanks Max. I have done that.
Thanks for the note drop. Yes, and I can see you've got some great responses there already. Bottoms up! -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating sub tasks in excel like MS project | Excel Discussion (Misc queries) | |||
Auditing excel sheets for everyday task | Excel Worksheet Functions | |||
How do I set up a trigger to remind me of tasks in excel | Setting up and Configuration of Excel | |||
reminder system for various tasks in excel | Excel Discussion (Misc queries) | |||
Schedule tasks in Excel | Excel Worksheet Functions |