Tjyylli

Provisioning profile doesn't include the application-identifier and keychain-access-groups entitlements

Sword in the Stone story where the sword was held in place by electromagnets

Science-fiction short story where space navy wanted hospital ships and settlers had guns mounted everywhere

How do anti-virus programs start at Windows boot?

Why doesn't the EU now just force the UK to choose between referendum and no-deal?

Be in awe of my brilliance!

Connecting top and bottom SMD component pads using via

What is IP squat space

How could a scammer know the apps on my phone / iTunes account?

RegionDifference for Cylinder and Cuboid

Schematic conventions for different supply rails

An Accountant Seeks the Help of a Mathematician

Informing my boss about remarks from a nasty colleague

Brexit - No Deal Rejection

Meaning of "SEVERA INDEOVI VAS" from 3rd Century slab

PlotLabels with equations not expressions

Where is the 1/8 CR apprentice in Volo's Guide to Monsters?

Make a transparent 448*448 image

It's a yearly task, alright

Why do passenger jet manufacturers design their planes with stall prevention systems?

When do we add an hyphen (-) to a complex adjective word?

Bash: What does "masking return values" mean?

How to make healing in an exploration game interesting

What does it mean to make a bootable LiveUSB?

Is it possible to make a two way connection between two cells

Merging two cells in multiple rowsTwo Way Link between excel workbooksMerge two cells “AAA” and “001” to get “AAA-001”?How can I make Excel conditionally highlight cells based on the contents of cells?Troubleshooting - How to mirror two cells from different sheets in Excel (2013) using VBAExcel - user input but retain formula for two circular cellsSelect two cells and subtract?How do I make Excel tab through merged cells in a protected sheet?How can I make Excel visually update cells?Data Validation Between 2 adjacent cells for all rows

0

How can I allow the user of my Excel sheet to enter data in either of two cells and have both cells updated?

• I have cell A4 in Sheet 1.


• I have cell B7 in Sheet 2.

If the user types 100% in cell A4 in Sheet 1, both cells A4 and B7 should say 100%.
If the user types 80% in cell B7 in Sheet 2, both cells A4 and B7 should say 80%.

I am using Excel 2013 but have access to 2016.

microsoft-excel

share|improve this question

asked Dec 20 '17 at 1:27

user823527user823527

2531417

bumped to the homepage by Community♦ 12 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This will require vba in a two worksheet_change events
  
  – Scott Craner
  Dec 20 '17 at 3:07
  

  
  
  
  

add a comment | 

0

How can I allow the user of my Excel sheet to enter data in either of two cells and have both cells updated?

• I have cell A4 in Sheet 1.


• I have cell B7 in Sheet 2.

If the user types 100% in cell A4 in Sheet 1, both cells A4 and B7 should say 100%.
If the user types 80% in cell B7 in Sheet 2, both cells A4 and B7 should say 80%.

I am using Excel 2013 but have access to 2016.

microsoft-excel

share|improve this question

asked Dec 20 '17 at 1:27

user823527user823527

2531417

bumped to the homepage by Community♦ 12 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  This will require vba in a two worksheet_change events
  
  – Scott Craner
  Dec 20 '17 at 3:07
  

  
  
  
  

add a comment | 

How can I allow the user of my Excel sheet to enter data in either of two cells and have both cells updated?

• I have cell A4 in Sheet 1.


• I have cell B7 in Sheet 2.

If the user types 100% in cell A4 in Sheet 1, both cells A4 and B7 should say 100%.
If the user types 80% in cell B7 in Sheet 2, both cells A4 and B7 should say 80%.

I am using Excel 2013 but have access to 2016.

microsoft-excel

share|improve this question

asked Dec 20 '17 at 1:27

user823527user823527

2531417

share|improve this question

asked Dec 20 '17 at 1:27

user823527user823527

2531417

share|improve this question

asked Dec 20 '17 at 1:27

user823527user823527

2531417

asked Dec 20 '17 at 1:27

user823527user823527

2531417

asked Dec 20 '17 at 1:27

user823527user823527

2531417

1 Answer
1

active

oldest

votes

1

If you want Dynamic link between Cells or Range of Cells, between Worksheets or even the Workbooks, for both way data entry, then VBA is the best solution. Since any Formula will not click it.

You need to write the following VBA code in both the Sheets.

For Sheet 1:

Private Sub Worksheet_change(ByVal Target As Range)

 If Not Intersect(Target, Range("A4")) Is Nothing Then

   If Target = Range("A4") Then

    Sheets("Sheet2").Range("B7").Value = Target.Value

  End If

End If



End Sub

For Sheet 2:

Private Sub Worksheet_change(ByVal Target As Range)



If Not Intersect(Target, Range("B7")) Is Nothing Then

  If Target = Range("B7") Then



    If Sheets("Sheet1").Range("A4").Value <> Target.Value Then

     Sheets("Sheet1").Range("A4").Value = Target.Value

    End If

  End If

End If



End Sub

NB: Code is sheet 2 will check if values in both Cells are not similar then
update the Sheet 1. If you don't need this, simply remove the If statement line.

Hope this help you, I've posted the VBA Code after it was tested by me.

share|improve this answer

edited Jul 19 '18 at 12:34

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Rajesh. I will see if this works.
  
  – user823527
  Dec 21 '17 at 1:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Its working,,. @user823527
  
  – Rajesh S
  Dec 21 '17 at 6:50
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I haven't done VBA in years, so I'm having trouble following how this works (and don't have ready access to Excel to test it and see what happens). My read of the question is that the entry of a new value in either cell triggers update of both cells to the new value. What is the trigger here that causes the new value to be recognized? It seems like the sequence that Excel runs the two macros would always cause the same cell to be recognized as having the "right" value. Is it that the entry causes only that sheet's macro to run? Also, why don't both macros need to be the same (reversed)?
  
  – fixer1234
  Jul 17 '18 at 22:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's between Sheet1A4 and Sheet2B7, What the Macro does, if U enter any value in Sheet1A4 it updates Sheet2B7 with the same value and the vise-versa. It's what the TWO WAY CONNECTION. Just now I've tested again, it's works successfully for me,, U also test will work.
  
  – Rajesh S
  Jul 18 '18 at 6:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This can not be correct as it changes value of A4 and B7 respectively, unconditionally where ever a change is made in either sheet. For example, if you enter "Oh, no!" in A1 (or any other cell) of either worksheet, the text "Oh, no!" appears in Sheet1.A4 and Sheet2.B7
  
  – Tom Brunberg
  Jul 18 '18 at 9:12
  

  
  
  
  

 | 
show 3 more comments

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1278626%2fis-it-possible-to-make-a-two-way-connection-between-two-cells%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

1

If you want Dynamic link between Cells or Range of Cells, between Worksheets or even the Workbooks, for both way data entry, then VBA is the best solution. Since any Formula will not click it.

You need to write the following VBA code in both the Sheets.

For Sheet 1:

Private Sub Worksheet_change(ByVal Target As Range)

 If Not Intersect(Target, Range("A4")) Is Nothing Then

   If Target = Range("A4") Then

    Sheets("Sheet2").Range("B7").Value = Target.Value

  End If

End If



End Sub

For Sheet 2:

Private Sub Worksheet_change(ByVal Target As Range)



If Not Intersect(Target, Range("B7")) Is Nothing Then

  If Target = Range("B7") Then



    If Sheets("Sheet1").Range("A4").Value <> Target.Value Then

     Sheets("Sheet1").Range("A4").Value = Target.Value

    End If

  End If

End If



End Sub

NB: Code is sheet 2 will check if values in both Cells are not similar then
update the Sheet 1. If you don't need this, simply remove the If statement line.

Hope this help you, I've posted the VBA Code after it was tested by me.

share|improve this answer

edited Jul 19 '18 at 12:34

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Rajesh. I will see if this works.
  
  – user823527
  Dec 21 '17 at 1:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Its working,,. @user823527
  
  – Rajesh S
  Dec 21 '17 at 6:50
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I haven't done VBA in years, so I'm having trouble following how this works (and don't have ready access to Excel to test it and see what happens). My read of the question is that the entry of a new value in either cell triggers update of both cells to the new value. What is the trigger here that causes the new value to be recognized? It seems like the sequence that Excel runs the two macros would always cause the same cell to be recognized as having the "right" value. Is it that the entry causes only that sheet's macro to run? Also, why don't both macros need to be the same (reversed)?
  
  – fixer1234
  Jul 17 '18 at 22:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's between Sheet1A4 and Sheet2B7, What the Macro does, if U enter any value in Sheet1A4 it updates Sheet2B7 with the same value and the vise-versa. It's what the TWO WAY CONNECTION. Just now I've tested again, it's works successfully for me,, U also test will work.
  
  – Rajesh S
  Jul 18 '18 at 6:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This can not be correct as it changes value of A4 and B7 respectively, unconditionally where ever a change is made in either sheet. For example, if you enter "Oh, no!" in A1 (or any other cell) of either worksheet, the text "Oh, no!" appears in Sheet1.A4 and Sheet2.B7
  
  – Tom Brunberg
  Jul 18 '18 at 9:12
  

  
  
  
  

 | 
show 3 more comments

1

If you want Dynamic link between Cells or Range of Cells, between Worksheets or even the Workbooks, for both way data entry, then VBA is the best solution. Since any Formula will not click it.

You need to write the following VBA code in both the Sheets.

For Sheet 1:

Private Sub Worksheet_change(ByVal Target As Range)

 If Not Intersect(Target, Range("A4")) Is Nothing Then

   If Target = Range("A4") Then

    Sheets("Sheet2").Range("B7").Value = Target.Value

  End If

End If



End Sub

For Sheet 2:

Private Sub Worksheet_change(ByVal Target As Range)



If Not Intersect(Target, Range("B7")) Is Nothing Then

  If Target = Range("B7") Then



    If Sheets("Sheet1").Range("A4").Value <> Target.Value Then

     Sheets("Sheet1").Range("A4").Value = Target.Value

    End If

  End If

End If



End Sub

NB: Code is sheet 2 will check if values in both Cells are not similar then
update the Sheet 1. If you don't need this, simply remove the If statement line.

Hope this help you, I've posted the VBA Code after it was tested by me.

share|improve this answer

edited Jul 19 '18 at 12:34

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you Rajesh. I will see if this works.
  
  – user823527
  Dec 21 '17 at 1:25
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Its working,,. @user823527
  
  – Rajesh S
  Dec 21 '17 at 6:50
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  I haven't done VBA in years, so I'm having trouble following how this works (and don't have ready access to Excel to test it and see what happens). My read of the question is that the entry of a new value in either cell triggers update of both cells to the new value. What is the trigger here that causes the new value to be recognized? It seems like the sequence that Excel runs the two macros would always cause the same cell to be recognized as having the "right" value. Is it that the entry causes only that sheet's macro to run? Also, why don't both macros need to be the same (reversed)?
  
  – fixer1234
  Jul 17 '18 at 22:43
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  It's between Sheet1A4 and Sheet2B7, What the Macro does, if U enter any value in Sheet1A4 it updates Sheet2B7 with the same value and the vise-versa. It's what the TWO WAY CONNECTION. Just now I've tested again, it's works successfully for me,, U also test will work.
  
  – Rajesh S
  Jul 18 '18 at 6:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  This can not be correct as it changes value of A4 and B7 respectively, unconditionally where ever a change is made in either sheet. For example, if you enter "Oh, no!" in A1 (or any other cell) of either worksheet, the text "Oh, no!" appears in Sheet1.A4 and Sheet2.B7
  
  – Tom Brunberg
  Jul 18 '18 at 9:12
  

  
  
  
  

 | 
show 3 more comments

If you want Dynamic link between Cells or Range of Cells, between Worksheets or even the Workbooks, for both way data entry, then VBA is the best solution. Since any Formula will not click it.

You need to write the following VBA code in both the Sheets.

For Sheet 1:

Private Sub Worksheet_change(ByVal Target As Range)

 If Not Intersect(Target, Range("A4")) Is Nothing Then

   If Target = Range("A4") Then

    Sheets("Sheet2").Range("B7").Value = Target.Value

  End If

End If



End Sub

For Sheet 2:

Private Sub Worksheet_change(ByVal Target As Range)



If Not Intersect(Target, Range("B7")) Is Nothing Then

  If Target = Range("B7") Then



    If Sheets("Sheet1").Range("A4").Value <> Target.Value Then

     Sheets("Sheet1").Range("A4").Value = Target.Value

    End If

  End If

End If



End Sub

NB: Code is sheet 2 will check if values in both Cells are not similar then
update the Sheet 1. If you don't need this, simply remove the If statement line.

Hope this help you, I've posted the VBA Code after it was tested by me.

share|improve this answer

edited Jul 19 '18 at 12:34

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524

Private Sub Worksheet_change(ByVal Target As Range)

 If Not Intersect(Target, Range("A4")) Is Nothing Then

   If Target = Range("A4") Then

    Sheets("Sheet2").Range("B7").Value = Target.Value

  End If

End If



End Sub

Private Sub Worksheet_change(ByVal Target As Range)



If Not Intersect(Target, Range("B7")) Is Nothing Then

  If Target = Range("B7") Then



    If Sheets("Sheet1").Range("A4").Value <> Target.Value Then

     Sheets("Sheet1").Range("A4").Value = Target.Value

    End If

  End If

End If



End Sub

share|improve this answer

edited Jul 19 '18 at 12:34

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524

answered Dec 20 '17 at 7:41

Rajesh SRajesh S

4,0701524