Two Way Linking / Mirror Data between cells

This is my first time on here so I am hoping someone can help me.
I am trying to do two way linking or mirror data with a twist. I’ve searched this and other forums but came up blank.
I want to be able to edit two different cells on different sheets, so if I change one cell the other one will mirror it and vice a versa. Here is my situation.
I keep track of company projects. Each project has its own sheet. I have a summary sheet that has specific information from each project sheet. For example, if I am on the Summary sheet and change the Contractor Name and the Start Date, the data will change on each project page. The kicker is, I will be adding sheets as I acquire more projects. Is it possible to have it automatically be set up so I don’t have to adjust the VBA or Macro?

I’ve attached a sample copy of my file.
Sample Two Way Linking.xlsx

Hi cfiore and welcome to ExcelForum,

I am not sure what you want mirrored by looking at your file. What you want to do can be done using VBA if it is known exactly what you want, e.g. what sheets to include or exclude, what cells to include or exclude.

See post #3 (includes a sample file) that solves a similar problem, except in that application, the mirroring is one one sheet.
http://www.excelforum.com/excel-prog...ml#post4258328

Lewis

Thank you Lewis, I will take a look at that link.
I know I am not very good at explaining things sometims.
What I want mirrored is:
The yellow cell on the Summary page for Alpha (Summary B5) should match the yellow cell in the Alpha sheet (Alpha B3), the yellow cell on the Summary page for Beta (Summary B6) should match the yellow cell in the Beta sheet (Beta B3).The Orange cell on the Summary page for Alpha (Summary C5) should match the yellow cell in the Alpha sheet (Alpha G1), the Orange cell on the Summary page for Beta (Summary C6) should match the Orange cell in the Beta sheet (Beta G1), and so on with each color.

Then when I add another sheet, for example Delta sheet, the cells have to mirror in the same way.

Hi,

Your description told me exactly what I needed to know. See the attached file which should get us started on what you want. The code was written using Excel 2003, so some of your Conditional Formatting is probably lost. We can take care of that later.

Your design introduces a philosophical conundrum. If there is a difference between what is on the 'Summary Sheet' and the 'Other Sheets', which value is the (MASTER VALUE) correct value. If there is a difference I provided two macros:
a. Update Summary Sheet From Other Sheets
b. Update Other Sheets From Summary Sheet

The software does not allow cut and paste of more than one cell. When big changes are needed, use the 'Disable Macros' macro to enable cut and paste of more than one cell. Make sure to 'Enable Macros' when your are done.


I used the following rules to implement the software:
a. Sheet Names must match names in Column A on the Summary Sheet.
b. The First Two Words in Row 3 of the Summary Sheet must match the titles on the Data Sheets.
c. All values on 'Alpha', 'Beta' etc. must be exactly one column to the Right of the 'Header Value'.
d. All items to be processed musts be manually changed (not changed by formula).
e. Only one Cell can be changed at a time.

Notes:
a. On Summary Sheet 'Date Start' changed to 'Start Date'.
b. Double Click Cell 'A1' on any sheet to go to the Summary Sheet.
c. On Summary Sheet 'L3' Commission was changed to 'Commission Paid'.

How the Software works:
a. Processing takes place each time a cell is manually changed.
b. Summary Sheet change:
(1) The 'Sheet Name' is obtained from Column 'A' of the Row changed.
(2) The 'Category' is is obtained from Row 3 of the 'Column' Changed.
(3) The 'Sheet Name' is searched for the 'Category'
(4) The cell to the right of the 'Category ' is updated with the new value.
c. Other Sheet change:
(1) The 'Category' is obtained from the cell immediately to the left of the cell that changed.
(2) 'Summary Sheet' Column 'A' is searched to obtain the row that contains the 'Sheet Name'.
(3) 'Summary Sheet' Row 3' is searched to obtain the column that contains the 'Category'.
(4) The 'Summary Sheet' is updated with the new value in the cell that contains the row and column found in steps 2 and 3.

To enable Macros and to Run Macros see the following:
http://office.microsoft.com/en-us/ex...010031071.aspx
http://office.microsoft.com/en-us/ex...010014113.aspx
If help is still needed do a google search for 'youtube excel enable macro' and/or 'youtube excel run macro'.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/
a. Click on any cell in the Excel Spreadsheet (may not be needed).
b. ALT-F11 to get to VBA.
c. CTRL-R to get project explorer (if it isn't already showing).
d. Double Click on a 'Module Name' in 'Project Explorer' to see code for that module.

Code is included in the following post.

Lewis

Code from the file associated with the previous post:

In the ThisWorkbook Code Module:

Please Login or Register  to view this content.

In ordinary code Module ModProcessChangeEvent:

Please Login or Register  to view this content.

Lewis

Hi Lewis.
This is GREAT!!!
Forgive me but I am a novice when it comes to writing this kind of codes. I need to make some adjustment by I am not sure how. I’ve looked over this code to see if I can modify it myself and I am lost.
Is it possible to have some of the cells on the Summary Sheet NOT mirror and have formulas? For example on the Summary Sheet to have Columns F (COR Issued), G (COR Approved), H (Total Contract) ,I (Expenses & Overhead), J (Gross Profit), M (Percent Commission), and M (Date Paid) to have formulas.
I don’t mind change the order of the columns on the Summary Sheet if that helps

Thank you but I think I am doing something wrong. I change the list of cell like you explained (i think it was like you explained)

'Other Sheet Constants
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Const sDataSheetCellstoPROCESS = "B3,B6,B13,E13,G1:G2"
Private Const nDataSheetStartROW = 1
Private Const nDataSheetEndROW = 13

Cells B10 and B12 were still mirroring on the data sheets

VBA Sample.jpg

Can you see what I did wrong?

Also, after I enter a value into one of the mirrored cells, it automatically transfers me to that page. For example: if I enter a value in Start Date on the summary sheet, when I exit the cell it transfers me to the data sheet. Is there a way to too turn that off?

Hi,

I apologize for the problems. You didn't do anything wrong. I did not implement the sDataSheetCellstoPROCESS array correctly.

I also commented out the two places that change the Sheet focus. I orginally implemented the code that way, so when you made a change that changed something on the other sheet, you would get positive feedback that the other sheet changed value, because the focus was now on the other sheet.

Try replacing the existing ProcessChangeEvent() with the code below, changes in red:

Please Login or Register  to view this content.

Please let me know if you have any more questions and/or problems.

Lewis

That works perfect!!!
Thank you so much.

Hi Lewis

I am also a novice with excel VBA. I also wish to do this mirror linking but I am not sure which or what to edit from your script. I attached my sample excel workbook for reference. Basically, my manager will do a monthly worksheet whereby he will input all information as per the sample. I, on the other part, need to compile all those information into the last worksheet "Admin Use" where I need to pass it to my Director as and when needed and he on the other hand prefers a long list of everything. So I have been copying and paste link all these while but there are times where I missed on the updated information keyed in on the monthly worksheets. If it is possible can include all formatting that are done on the worksheets like text wrap, background fill and change of font colour. I really appreciate for any help given. I am using Excel 2016. Thank you.