Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Conditional Formatting between two Workbooks using extra columns

How to create a conditional format and a warning for the difference of two dates in two different Workbook

If you want to create a Conditional formatting when you use two workbooks in Excel, then read this tutorial and you will see how you can create conditional create a warning that the two dates or different data for a particular condition. About the basics features Conditional Formatting I have already written to the respective link.

In this situation we have two workbooks that are located in the same folder (directory)
  • Workbook1.xlsx (original data with which to compare data Workbook2)
  • Workbook2.xlsx (destination information in which we create conditional formatting if different dates from Workbook1)
In the workbook "Workbook1" are unique names of persons and related data or dates. 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 excel warn about not entering the correct date for the person concerned. This problem can be solved using Conditional formatting between two workbooks.

In the picture below you see the data in the workbook 'Workbook1'


In this workbook "Workbook1.xlsx" are the original dates (data) with which we compare dates in another workbook, "Workbook2.xlsx".

Source data for Conditional Formatting

CF warning, the differences date from two workbooks using support columns

In this first example of solving problems in the workbook 'Workbook2.xlsx' (see figure below), I have used two extra columns and VLOOKUP formula. For this first example I created to solve two extra column (which you can hide if you interfere in visual terms) to copy dates for each person in column 'A'. These dates in columns 'E' and 'F' in the workbook 'Workbook2.xlsx' are copied using the VLOOKUP formula from a workbook 'Workbook1.xlsx' which is the source of data.

The picture below shows Workbook2.xlsx.

Conditional formatting between two workbooks in Excel

Note: If you formulas do not work, replace the separator semicolon with a comma
IMPORTANT: In this case, the original workbook 'Workbook1.xlsx' does not have to be opened to update the data

The formula in cell 'E3' is as follows: (copy the formula to the last row)

=VLOOKUP($A3;[workbook1.xlsx]Sheet1!$A$2:$D$20;3;FALSE)
=VLOOKUP($A3;[workbook1.xlsx]Sheet1!$A$2:$D$20;COLUMN(C1);FALSE)
=> You can copy this formula to the right
=VLOOKUP($A3;workbook1.xlsx!alldata;3;FALSE)

this formula can be written in the following formats if the workbook is in another location, or if the source workbook is not open, it should be confirmed by the "Update" when you open the workbook:

=VLOOKUP($A3;'C:\Temp\[workbook1.xlsx]Sheet1'!$A$2:$D$20;3;FALSE)
=VLOOKUP($A3;'C:\Temp\[workbook1.xlsx]Sheet1'!$A$2:$D$20;COLUMN(C1);FALSE)
=> You can copy this formula to the right

The formula in cell 'F3' is as follows: (copy the formula to the last row)

=VLOOKUP($A3;[workbook1.xlsx]Sheet1!$A$2:$D$20;4;FALSE)
=VLOOKUP($A3;[workbook1.xlsx]Sheet1!$A$2:$D$20;COLUMN(D1);FALSE)
=VLOOKUP($A3;workbook1.xlsx!alldata;3;FALSE)


this formula can be written in the following formats if the workbook is in another location, or if the source workbook is not open, it should be confirmed by the "Update" when you open the workbook:

=VLOOKUP($A3;'C:\Temp\[workbook1.xlsx]Sheet1'!$A$2:$D$20;4;FALSE)
=VLOOKUP($A3;'C:\Temp\[workbook1.xlsx]Sheet1'!$A$2:$D$20;COLUMN(D1);FALSE)


NOTE: If you use Excel function 'COLUMN (C1)' in the formula, then simply copy the formula of 'E3' to 'F3' all the way down to the last row, because in this case the formula correctly used last but one argument col_index_num, VLOOKUP functions.

Set the Conditional Formatting is done for each column separately, so that follows (see the two pictures below):

The formula for Conditional Formatting in the range of cells 'B3: B20' is as follows:

=IF($B3<>$E3;TRUE;FALSE)

Conditional formatting for column B

The formula for Conditional formatting in the range of cells 'C3: C20' is as follows:

=IF($C3<>$F3;TRUE;FALSE)

Conditional formatting for C column

In this way we are using Conditional Formatting solve a visual warning in the event that the dates are different for each individual who from between two workbooks. If you do not want to use the auxiliary columns then see the next tutorial, "Conditional Formatting between two Workbooks"