1

I have 2 sheets in the same workbook containing the same values. I want the Sheets to mirror each other so that whenever I choose a new value in either sheet 1 or sheet 2, the value on the other sheet is updated to match the newly selected value. Is this possible?

3

1 Answer 1

0

Excel can find Duplicate Rows easily if you are ready to apply Professional Approach.

Method 1:

You need a Column which contains a unique identifier for records, for example ID or Account Number. Now by referencing this in the Formula, you can find Duplicates even if the data in other columns doesn't perfectly match up.

Write this Formula anywhere in blank Column, if you wish you can write it in both the Sheets, to cross check each other.

=IF(ISERROR(MATCH(A1,Sheet1!A:A,0)),"","Duplicate")

NB: This is written in Sheet 2, when in Sheet 2 you want to use it for Sheet 1, only you need to Replace the Sheet Name in Formula.

Method 2:

Follow these in steps.

  1. Select the Column A in Sheet 1.

  2. Hit Conditional Formatting TAB.

  3. Select New Rule and choose Use a formula.

  4. Enter the following Formula.

=COUNTIF(Sheet2!$A$1:$A$100,A1)>0

  1. Choose appropriate Formatting for the rule.

NB: You can use the Mouse to select the cells in the Second Sheet, will Count the cells if any, that also appear in the Second Sheet, and apply the Cell Formatting if there is at least one.

Hope this help you.

3
  • I think you have misunderstood the question. I doesn't ask how to identify duplicate rows. It asks how to have changes in either sheet be automatically reflected in the other sheet.
    – Blackwood
    Jan 19, 2018 at 13:22
  • @Blackwood,, OP said, I need mirror image for the sheets, mean both should IDENTICAL, also OP has written, When picks NEW VALUE it must check into other and if not found then UPDATE with new one. So to solve it the technically required 1st action is, CHECK the NEW VALUE when entered in Sheet 1, whether is available in Sheet 2 or not. After this only UPDATE is required. Jan 20, 2018 at 6:29
  • Look, to automate this VBA Code is better then use Function and OP has not mentioned about the VBA. Jan 20, 2018 at 6:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .