Home

make 2 cells on different pages mirror each other.

%3CLINGO-SUB%20id%3D%22lingo-sub-1263070%22%20slang%3D%22en-US%22%3Emake%202%20cells%20on%20different%20pages%20mirror%20each%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1263070%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20I%20need%20to%20do%20is%20as%20follows.%3C%2FP%3E%3CP%3ESheet%201%20Cell%20A1%20Mirror%20Sheet%202%26nbsp%3B%20Cell%20D4%3C%2FP%3E%3CP%3ESheet%201%20Cell%20A2%20Mirror%20Sheet%203%20Cell%20D4%3C%2FP%3E%3CP%3ESheet%202%20Cell%20D4%20Mirror%20Sheet%201%26nbsp%3B%20Cell%20A1%3C%2FP%3E%3CP%3ESheet%203%20Cell%20D4%20Mirror%20Sheet%201%20Cell%20A2%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20be%20able%20to%20change%20the%20value%20on%20either%20side%20and%20it%20change%20the%20other.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1263070%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1263540%22%20slang%3D%22en-US%22%3ERe%3A%20make%202%20cells%20on%20different%20pages%20mirror%20each%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1263540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600154%22%20target%3D%22_blank%22%3E%40mark_bates%3C%2FA%3E%26nbsp%3BYou%20would%20need%20some%20VBA%20coding%20in%20each%20of%20the%20worksheets%20own%20code%20area.%20The%20code%20checks%20if%20there%20was%20a%20change%20in%20the%20cell(s)%20specified%20and%20then%20changes%20the%20other%20cell%2C%20but%20only%20if%20the%20other%20cell%20has%20a%20different%20value.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20attached%20workbook%20contains%20a%20working%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1265081%22%20slang%3D%22en-US%22%3ERe%3A%20make%202%20cells%20on%20different%20pages%20mirror%20each%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1265081%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EThank%20you%20for%20the%20info.%26nbsp%3B%20I%20am%20a%20contractor%20and%20I%20am%20trying%20to%20improve%20the%20excel%20spreadsheet%20I%20made%20to%20bid%20jobs.%26nbsp%3B%20I%20am%20learning%20a%20lot%20about%20how%20it%20all%20works%20but%20I%20must%20admit%20I%20am%20surely%20a%20novice%20at%20best.%20Would%20you%20be%20able%20to%20tell%20me%20the%20steps%20to%20implement%20those%20codes%3F%26nbsp%3B%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1266555%22%20slang%3D%22en-US%22%3ERe%3A%20make%202%20cells%20on%20different%20pages%20mirror%20each%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1266555%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600154%22%20target%3D%22_blank%22%3E%40mark_bates%3C%2FA%3E%26nbsp%3BLet%20me%20first%20ask%20the%20question%20if%20you%20only%20need%20to%20mirror%20a%20few%20cells%2C%20like%20in%20your%20example.%20Because%2C%20when%20you%20have%20a%20lot%20of%20them%20and%20on%20a%20lot%20of%20sheets%2C%20it%20could%20easily%20get%20out%20of%20hand.%20And%20since%20you%20are%20trying%20to%20improve%20an%20existing%20sheet%2C%20I%20wonder%20if%20you%20can't%20set%20it%20up%20in%20a%20different%20way%20so%20that%20the%20need%20to%20mirror%20cells%20in%20the%20way%20you%20describe%20it%20on%20different%20sheets%20goes%20away.%20I%20suspect%20that%20these%20mirrored%20cells%20are%20parameters%20of%20some%20sort%20that%20need%20to%20change%20in%20both%20sheets%2C%20if%20certain%20conditions%20are%20met.%20If%20so%2C%20you%20will%20be%20better%20off%20creating%20a%20separate%20table%20with%20all%20these%20parameters%20and%20rules%2C%20and%20link%20all%20relevant%20cells%20to%20this%20table.%20There%20are%20many%20ways%20to%20achieve%20this%20but%20it%20depends%20a%20bit%20on%20how%20you%20have%20set-up%20your%20data.%20Would%20also%20be%20good%20to%20know%20which%20Excel%20version%20you%20are%20on.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268458%22%20slang%3D%22en-US%22%3ERe%3A%20make%202%20cells%20on%20different%20pages%20mirror%20each%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268458%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3Eif%20you%20look%20at%20this%20file%20I%20think%20it%20would%20help.%26nbsp%3B%20On%20page%20Main%2C%20Cells%20E10-E44%20are%20the%20cells%20I%20need%20to%20be%20able%20to%20mirror.%26nbsp%3B%20So%20E10%20will%20mirror%20to%20Sheet%201%2C%20Cell%20I30.%26nbsp%3B%20E11%20will%20mirror%20to%20Sheet%202%2C%20Cell%20I30%20and%20so%20on.%26nbsp%3B%20What%20this%20does%20is%20gives%20me%20the%20ability%20to%20adjust%20my%20unit%20prices%20and%20it%20reflect%20on%20the%20total%20page%20and%20the%20specific%20item%20page.%26nbsp%3B%20When%20we%20bid%20work%20our%20numbers%20keep%20changing%20down%20to%205%20or%20less%20minutes%20prior%20to%20the%20bid%20deadline.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20need%20to%20learn%20how%20to%20make%20a%20drop%20down%20menu%20on%20Sheet%201%20Cell%20A19%20that%20uses%20the%20list%20on%20the%20Equip%20Page%20A1.%26nbsp%3B%20I%20can%20do%20that%20but%20I%20cant%20seem%20to%20figure%20out%20how%20to%20make%20it%20so%20when%20I%20select%20an%20equipment%20in%20the%20drop%20down%20menu%20it%20also%20has%20a%20rate%20attached%20to%20that%20equipment%20that%20I%20can%20then%20multiply%20by%20adding%20hours%20into%20Sheet%201%20Cell%20D19.%20I%20appreciate%20the%20help.%26nbsp%3B%20Been%20Working%20on%20this%20for%20days.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1268927%22%20slang%3D%22en-US%22%3ERe%3A%20make%202%20cells%20on%20different%20pages%20mirror%20each%20other.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1268927%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F600154%22%20target%3D%22_blank%22%3E%40mark_bates%3C%2FA%3E%26nbsp%3BSaw%20your%20file%20was%20an%20%22xls%22%20file.%20An%20old%20file%20type!%20It%20gave%20a%20warning%20that%20it%20contained%20macros%20when%20i%20opened%20it.%20But%20I%20found%20no%20macro's%2C%20just%20two%20empty%20User%20Forms%20and%20i%20could%20not%20do%20much%20with%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20understand%20that%20you%20will%20have%2035%20sheets%2C%20i.e.%20one%20for%20every%20item%20on%20your%20Main%20sheet%2C%20and%20Sheet1%20is%20the%20layout%20you%20are%20going%20to%20have%20for%20all%20of%20them.%26nbsp%3BAnd%20then%20you%20want%20each%20cell%20in%20E10%20to%20E44%20linked%20to%20a%20corresponding%20cell%20in%20each%20of%20the%2035%20underlying%20sheets%3F%20But%20%22E--%22%20in%20the%20main%20sheet%20has%20%22Unit%20Price%22.%20I30%20in%20the%20underlying%20sheets%20has%20%22Units%22.%20How%20did%20you%20think%20to%20mirror%20a%20price%20with%20a%20quantity%3F%20But%20perhaps%20I%20misunderstood.%20Sorry!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regard%20to%20creating%20the%20drop%20down%2C%20that%20easy%20but%20I%20couldn't%20do%20it%20properly%20due%20to%20the%20due%20to%20the%20problems%20described%20above.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

What I need to do is as follows.

Sheet 1 Cell A1 Mirror Sheet 2  Cell D4

Sheet 1 Cell A2 Mirror Sheet 3 Cell D4

Sheet 2 Cell D4 Mirror Sheet 1  Cell A1

Sheet 3 Cell D4 Mirror Sheet 1 Cell A2

 

I need to be able to change the value on either side and it change the other. 

 

5 Replies
Highlighted

@mark_bates You would need some VBA coding in each of the worksheets own code area. The code checks if there was a change in the cell(s) specified and then changes the other cell, but only if the other cell has a different value. 

The attached workbook contains a working example.

 

Highlighted

@Riny_van_EekelenThank you for the info.  I am a contractor and I am trying to improve the excel spreadsheet I made to bid jobs.  I am learning a lot about how it all works but I must admit I am surely a novice at best. Would you be able to tell me the steps to implement those codes?  Thanks in advance.

Highlighted

@mark_bates Let me first ask the question if you only need to mirror a few cells, like in your example. Because, when you have a lot of them and on a lot of sheets, it could easily get out of hand. And since you are trying to improve an existing sheet, I wonder if you can't set it up in a different way so that the need to mirror cells in the way you describe it on different sheets goes away. I suspect that these mirrored cells are parameters of some sort that need to change in both sheets, if certain conditions are met. If so, you will be better off creating a separate table with all these parameters and rules, and link all relevant cells to this table. There are many ways to achieve this but it depends a bit on how you have set-up your data. Would also be good to know which Excel version you are on. 

Highlighted

@Riny_van_Eekelenif you look at this file I think it would help.  On page Main, Cells E10-E44 are the cells I need to be able to mirror.  So E10 will mirror to Sheet 1, Cell I30.  E11 will mirror to Sheet 2, Cell I30 and so on.  What this does is gives me the ability to adjust my unit prices and it reflect on the total page and the specific item page.  When we bid work our numbers keep changing down to 5 or less minutes prior to the bid deadline.  

 

I also need to learn how to make a drop down menu on Sheet 1 Cell A19 that uses the list on the Equip Page A1.  I can do that but I cant seem to figure out how to make it so when I select an equipment in the drop down menu it also has a rate attached to that equipment that I can then multiply by adding hours into Sheet 1 Cell D19. I appreciate the help.  Been Working on this for days. 

Highlighted

@mark_bates Saw your file was an "xls" file. An old file type! It gave a warning that it contained macros when i opened it. But I found no macro's, just two empty User Forms and i could not do much with it.

 

I understand that you will have 35 sheets, i.e. one for every item on your Main sheet, and Sheet1 is the layout you are going to have for all of them. And then you want each cell in E10 to E44 linked to a corresponding cell in each of the 35 underlying sheets? But "E--" in the main sheet has "Unit Price". I30 in the underlying sheets has "Units". How did you think to mirror a price with a quantity? But perhaps I misunderstood. Sorry!

 

With regard to creating the drop down, that easy but I couldn't do it properly due to the due to the problems described above.