Auditing excel for everyday tasks

[test]

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

 

[Max]

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

 

[Shane Devenshire]

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,""))

 

[test]

 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












- 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

 

[Max]

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
---
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

 

[test]

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
---
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.

 

[Max]

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
---