?
Solved

Is there a way to mark conditional format on more than one worksheet

Posted on 2015-01-11
3
Medium Priority
?
101 Views
Last Modified: 2015-01-12
Was looking around and haven't seen anything really for this, not sure if formula or VB code, but I have a report that is given at the end of each week.  I'm looking to see if there's a way that a duplicate check can be done on each day where it will look at the other sheet and mark for a duplicate based on the column marked "Invoice".

So if there's an invoice# 123 on Monday and Wednesday, it will highlight invoice red on both days. Is this possible?
ExampleFile.xlsx
0
Comment
Question by:NYANBCNY32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 400 total points
ID: 40543713
Hello,

you can use a Countif for each sheet and add up the results. If the total  is greater than one, then there is a duplicate in one of the other sheets. For example, create a new rule on column C, starting in cell C2 using this formula:

=COUNTIF(Monday!C:C,C2)+COUNTIF(Tuesday!C:C,C2)+COUNTIF(Wednesday!C:C,C2)+COUNTIF(Thursday!C:C,C2)+COUNTIF(Friday!C:C,C2)>1

Extend the format down to cover all rows and copy the format to column C in all other sheets.

Note that Excel before version 2010 does not allow references to other sheets in conditional formatting formulas. In this case you would need to create named ranges and refer to these instead,

cheers, teylyn
0
 
LVL 6

Accepted Solution

by:
Let's Go earned 1600 total points
ID: 40543757
If you have lots of data, it may be easier for the user using multiple conditional formatting conditions.  For example,
if there is a duplicate within the day: colour the cell red
if there is a duplicate with Tuesday: colour the cell orange
if there is a duplicate with Wednesday: colour the cell yellow
etc.

Tests would be:
To check for duplicates within a day: COUNTIF(C:C,$c3)>1
To check for duplicates between a day and Tuesday (you would not use this on Tuesday's cells): =NOT(ISNA(VLOOKUP($C3,Tuesday!$C:$C,1,FALSE)))
with similar tests for each other day

A sample of this method (formatting applied to Monday only) is attached.
ExampleFile2.xlsx
0
 

Author Closing Comment

by:NYANBCNY32
ID: 40545924
Thank you both for the help on this piece, it is greatly appreciated and working beautifully.  Cheers.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Simple Linear Regression
Six Sigma Control Plans

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question