[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

Count a condition in a group of cells and flag that result on a cell in another worksheet

Hi All, I have a workbook that has summary list of all our branches and the date they were auditted on one sheet. On another sheet in the same work book I have the issues obtained from the audits which I can then pivot. I have a "completed' column that is filled 'Y' or 'N' from a drop list in the data sheet for each audit item.  In Excel 2003 is it possible to flag a cell on the summary sheet i.e. 'Complete' or 'Not Complete' or colour the cell depending on the status of the 'completed' column on the data sheet. The result I am hoping for is that while items are outstanding the branch status will be indicated and this will alter when there are no more outstanding items
0
Craig Anderson
Asked:
Craig Anderson
  • 2
2 Solutions
 
dlmilleCommented:
Yes, you can.

On the summary sheet, you'll need a formula that compares two data points:

1.  The count of items in the data sheet that has the hi-level audit item, and
2.  The count of items in the datasheet at the hi-level autit item where the issue/compleded column has been marked Y

When you compare those two data points, if they are equal, then your audit item is "Complete".  Otherwise, its "Not Complete".

Its simple enough to either mark complete, or use conditional formatting, or both.

See attached simple mockup demonstrating this (using rows 2-5000 on the detail sheet:


AuditWorkbook-r1.xls
0
 
dlmilleCommented:
Note, when you have the formulas working for YOUR example (feel free to upload a brief, non-sensitive mockup, if you need help), then you'll probably want to put some error checking in your formulas...

E.g.,

=IF(ISERROR(the formula),"N/A - or something like Not Found",the formula)

Cheers,

Dave
0
 
Glenn RayExcel VBA DeveloperCommented:
dlmille's/Dave's example works, but here is another example that I think more-closely follows your description.

The SUMPRODUCT function is used here also, but in a slightly different manner.  The range
      'Audit Details'!$A$1:$C$69
could assigned a range name to make formulas more readable.  

 Branch-Audit-Example.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now