How to Create Conditional Formatting Between two Workbooks Without Extra Columns
In this second Excel example I will show a different way of how we can use Conditional Formatting to create a visual warning in the event that the data do not match in the two Excel Workbook .If you want to create Conditional Formatting (CF) when using two workbooks in Excel, then read this and the previous tutorial and observe how you can create conditional create a warning to two dates or different data for a particular condition. About basics Conditional Formatting I have already written to the respective link.
In this second case there are two Excel Workbook that are placed in the same folder (C:\Test or C:\Temp).
- Workbook1.xlsx (source data with which to compare data Workbook2)
- Workbook2.xlsx (destination data in which we create a conditional format for the case to be different the dates of Workbook1)
In the workbook "Workbook1" are UNIQUE names of persons and corresponding dates or data. Our goal is to enter the dates for a particular person in a workbook "Workbook2", and if you enter the wrong date or the date is different for a certain person then we want to be Excel, warn about not entering the correct date for the person concerned. This problem can be solved using the Conditional Formatting between two Workbooks.
Note: If you formulas do not work, replace the separator semicolon with a comma
IMPORTANT: In this second case, the original workbook 'workbook1.xlsx' must be opened in order to update the data in the workbook 'workbook2.xlsx'
In the picture below you see the data in Workbook1
In this workbook "Workbook1.xlsx" are the original data (dates) with which we compare dates in the second workbook, "Workbook2.xlsx". This Excel Workbook I have defined names for certain cell ranges (if you do not know how to define a name for the cell or range then see respective link).
In the following tutorial, followed by the names of defined cell range or range of data in Workbook1.xlsx.
- alldata => =Sheet1!$A$2:$D$20 (Range for all date into ranks, This range is used for the VLOOKUP formula in Workbook2.xlsx)
- date1 => =Sheet1!$C$2:$C$20 (Start Date)
- date2 => =Sheet1!$D$2:$D$20 (End Date)
Conditional Formatting Tetween Two Excel Workbooks
Solving that particular problem at the previous of the tutorial, I've made without using additional columns (as opposed to the previous tutorial).
On Sheet1 in 'Workbook2.xlsx' there are only three columns. The column 'B' and the column 'C' set the Conditional Formatting (see figure below).
It is known that in Excel we can define the name of the cell or range of cells (range of data). In this case the 'Workbook2.xlsx' I have not defined the name of the cell, but I have defined the name of the formula. So, the first step I defined the names for the two formulas in the Name Manager (the formula used in the previous tutorial with extra columns).
Defined Names to the following formula: (there is a difference between formulas when Workbook1 is open and when Workbook1 not open, although both workbook in the same folder). You see the image below.
In the formulas below, used a named range data => alldata => =Sheet1!$A$2:$D$20
Defining name of formula in Excel Name Manager
If 'Workbook1.xlsx' is open
The formula is defined as "ColumnB" =VLOOKUP(Sheet1!A2;workbook1.xlsx!alldata;3;FALSE)
The formula is defined as "ColumnC" =VLOOKUP(Sheet1!A2;workbook1.xlsx!alldata;4;FALSE)
If 'Workbook1.xlsx' is not open
The formula is defined as "ColumnB" =VLOOKUP(Sheet1!A2;'C:\Temp\workbook1.xlsx'!alldata;3;FALSE)
The formula is defined as "ColumnC" =VLOOKUP(Sheet1!A2;'C:\Temp\workbook1.xlsx'!alldata;4;FALSE)
NOTE: Both Workbook open.
In the next step you need to apply Conditional Formatting to cell ranges in 'Workbook2.xlsx' where we want to have a visual warning if dates are not identical in both Workbook for the person concerned. Name of the person's condition in the VLOOKUP formula. This range varies depending on how much and in the future plan to have rows of data.
Selected a range of cells' B2:B20. Start = Conditional Formatting> New Rule => click "Use a formula to determine which cells to format" and then in the 'Refers to' below, enter the following formula
=IF($B2<>ColumnB;TRUE;FALSE) set the Format, click OK => Apply
Selected a range of cells 'C2:C20'. Start = Conditional Formatting> New Rule => click "Use a formula to determine which cells to format" and then in the field 'Refers to:' below, enter the following formula.
=IF($C2<>ColumnC;TRUE;FALSE) set the Format, click OK => Apply
After setting the Conditional Formatting in the 'Workbook2.xlsx' visually you can to spot the differences between the Dates (data) contained in Workbook1 and Workbook2.
Setting Conditional Formatting rules for column B (picture below).
Setting Conditional Formatting rules for column C (image below).
Download a template example file in this tutorial. Unzip the ZIP file to the C: partition of your HDD. Read this tutorial, and yourself set formula (copy from the website) as well as the appointment in order to understand how the Conditional Formatting between two Workbooks.