Duplicate Databases (2 worksheets) - No Master

rlong98

New Member
Joined
Dec 6, 2019
Messages
11
Office Version
365, 2016, 2013
Platform
Windows, Web
Can there be 2 identical Databases (2 worksheets) that synchronize both ways (insert to 1 updates 2 / update 2 - updates 1)?

Here's the scenario

I want 2 worksheets to be identical, where one sheet will contain A-P and the other Sheet will contain Q-Z.
But if I update Worksheet 1 or 2 they will both Synchronize.

GOAL (if you have a better solution)
1. Sheet1 (Table 1) will have a unique ID with various data
1. Second Sheet will need to contain financial data regarding Sheet 1, but be applied to the Unique ID

When we enter in a new line in the DB and add the ID (new row) - and enter the rest of the data in A-P (We don't want to show the financial data on this Sheet 1
Then on Sheet 2 - we want the newly added ID to show with some other columns from Sheet 1 as well as Q-Z Financial data to be typed in ON SHEET 2 and have it save.

What I couldn't find out how to so is when a new ID is made in the DB Table - that it auto populates to Sheet 2. (I don't care to use an Index formula - but if I have to I will)
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,022
Office Version
365
Platform
Windows
Another option for you to consider

Unless you have another reason for keeping the data separated .... maintain one sheet ("Master") and pull pertinent data into Sheet1 and Sheet2 automatically when either sheet is activated
- avoids having to keep track of users deleting, amending or amending ID's in separate sheets
- nothing can go "out of wack" - data is always overwritten with latest data from "Master"

in Sheet1's code module (select Sheet1 \ right click sheet tab \ View Code \ paste code into that module)
VBA Code:
Private Sub Worksheet_Activate()
    Sheets("Master").Range("A:P").Copy Range("A1")
End Sub
in Sheet2's code module
VBA Code:
Private Sub Worksheet_Activate()
    Sheets("Master").Range("A:A").Copy Range("A1")
    Sheets("Master").Range("Q:Z").Copy Range("B1")
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,022
Office Version
365
Platform
Windows
Suggestion in post#2 is the simplest solution. But it may not be what you want.

If so, there is a requirement to consider everything that could lead to the data in the 2 sheets going out of sync.

1. Does column A on both Sheet1 and Sheet2 contain the unique ID ?
2. Are users able to ...
... delete an ID on either sheet ?
... delete a row on either sheet ?
... edit an ID on either sheet ?
... create a new ID on either sheet ?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,144
Messages
5,466,934
Members
406,511
Latest member
markflayd

This Week's Hot Topics

Top