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?
-
You can do this by holding Shift and clicking both Sheet tabs.– wysiwygJan 18, 2018 at 23:18
-
Welcome to Super User! Since the answer is probably not specific to only a single version of Excel, it's best to include the version-less excel tag as well.– jpaughJan 19, 2018 at 0:05
-
This sounds like a common question that we get frequently. Look at How to either have direct data input or cell value from formula, Two related cells: give a value in one, calculate the other, and vice versa? and How to have a formula in a cell and allow a user to enter data?, and see whether they answer your question.– Scott - Слава УкраїніJan 27, 2018 at 20:43
1 Answer
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.
Select the Column A in Sheet 1.
Hit Conditional Formatting TAB.
Select New Rule and choose Use a formula.
Enter the following Formula.
=COUNTIF(Sheet2!$A$1:$A$100,A1)>0
- 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.
-
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. 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