Can I Edit A Cell Between Multiple Sheets In Different Locations?

[figuare9]

I'm trying to edit cells between sheets. Let's say for instance D5 on Sheet 2, and F5 In Sheet 4. Both of these cells are the same value, and I'd like to be able to update the cells in either sheet, depending on where I am in my workbook at the time.


I tried to link them, but excel gave me a warning about it? My attempt was clicking on D5 in Sheet 2, and typing =F5 in sheet 4. But this only allows a one way link so to speak. I'd like it to go both ways, but unfortunately I don't know enough about excel to figure it out. Google results in really the only method I figured out so far.

I'd really appreciate some help with this!

 

[Johnny C]

it is possible, with VBA. I'm guessing that's more advanced than you would care for. How many of these 2-way 'linked' cells are there?

 

[figuare9]

29... I've made a pretty in depth sheet. I have experience with web design but I'm a bit more rusty with excel.. So essentially I have something designed similar to a website. I have a front page, and an employee directory. However, I also have a spreadsheet with multiple values. So I'm looking to be able edit values per employee, and per the main spreadsheet with everything on it as well. They're the same value, just in different locations. It's an ease of access type thing essentially.

 

[Johnny C]

Fine. Are you familiar with Excel VBA?

First you need to save your workbook as a macro-enabled or binary format (.xlsm or .xlsb). I prefer xlsb myself but that's personal preference.

Second, on each tab where, if you make a change, you want a value elsewhere to change, right click the tab and select 'View Code'.

If you're familiar with web page design this will be simple for you.

This will open up the VBA editor.

Lets start with the example you quoted. Select Sheet2, right click the tab and click 'View Code'

Copy and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.AddressLocal = "$D$5" And Worksheets("Sheet4").Range("$F$5").Value <> Target.Value Then Worksheets("Sheet4").Range("$F$5").Value = Target.Value
End Sub

Now when you change the value in Sheet2!D5 it should appear in Sheet4!F5

Now go onto Sheet4, View Code, and copy & Paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.AddressLocal = "$F$5" And Worksheets("Sheet2").Range("$D$5").Value <> Target.Value Then Worksheets("Sheet2").Range("$D$5").Value = Target.Value
End Sub

This is the other way round, so will send the value here back to the other sheet and stop the infinite loop (see note below).

If you have more cells on Sheet2 that you want replicating, copy the if Target.AddressLocal line and change the D5 to where you want the value copied from and sheet4 F5 to wherever you need the value to go.

repeat this on each tab for sending both ways.

The way this works, each sheet can have sub macros that work when something happens on that worksheet. In this case, the code runs whenever you change a value on the worksheet.
The Target parameter is the cell you changed. This has an AddressLocal property that tells you the cell reference. So you can do an If statement to test if the cell is one you want to replicate.
The target also has a value property which is - not surprisingly - the value in the cell.
The place you want to send the value to is identified by the worksheet object and the range object within that worksheet, and you can set the value property of that range.

You need the condition

And Worksheets("Sheet4").Range("$F$5").Value <> Target.Value

because if you don't, you change Sheet2!D5 which changes Sheet4!F5. This causes a worksheet change event on Sheet4, which calls the code that sends the change back, which calls the code that sends it back to Sheet4 again and so on, an infinite loop. You can get out of it by hitting escape if something goes wrong, but that condition means the loop stops once the value is the same in both cells.

So all you need to do is copy and paste this, add all the target cells you want to replicate, and specify where you want the value to go.

Then if you have other sheets, copy the code and repeat as necessary there, just amend the target references on that sheet you want to replicate and the destination references.

Simples

Have a go and post back if you have a problem. You shouldn't though, I just checked it and it worked for me.

 

[Johnny C]

PS if you insert rows or columns or cells on either the source or destination sheet, don't forget to amend the VBA to the new references or it will stop working ... if you went down the route of named ranges it would get more complicated.

 

[figuare9]

This is incredible.. Thank you soo much for this! I really appreciate your help. Very good tutorial!!! You're amazing! Exactly what I was looking for!!!!!! Although I didn't think it'd be this far in depth.. ha. It's perfect though. Thanks again!!

 

[figuare9]

Okay, so after attempting this, I'm getting an error. Run-time error '9'
Subscript out of range. When I Debug it takes me to the first code. Highlighted here..

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.AddressLocal = "$D$5" And Worksheets("Sheet4").Range("$F$5").Value <> Target.Value Then Worksheets("Sheet4").Range("$F$5").Value = Target.Value
End Sub

I did attempt this multiple times.. I must be missing something?

 

[Johnny C]

Ok. First the obvious ... is your Sheet4 called "Sheet4" or does it have a space "Sheet 4"?

If that's not the case, try this.

Private Sub Worksheet_Change(ByVal Target As Range)
msgbox "Target.AddressLocal = " & Target.AddressLocal 
msgbox "Target.Value = " & Target.Value
msgbox "Worksheet F5 = " & Worksheets("Sheet4").Range("$F$5").Value 
If Target.AddressLocal = "$D$5" And Worksheets("Sheet4").Range("$F$5").Value <> Target.Value Then Worksheets("Sheet4").Range("$F$5").Value = Target.Value
End Sub

What this will do is fire up a message box for each of the elements of that line. It should crash on the line where the value causes an error. It works fine for me so we'll have to debug it step by step online.

Also which version of Excel are you using? It shouldn't make a difference, this is bog standard VBA but it might help if it's something obscure if it's an old copy.

 

[Johnny C]

Here's another thought... How many cells have you got selected? If you had more than one, it would be trying to cram the values from all the cells into 1 cell which would cause it to crash.

You could also try this:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.AddressLocal = "$D$5" And Worksheets("Sheet4").Range("$F$5").Value <> Target.Value Then Worksheets("Sheet4").Range("$F$5").Value = Target.Value
End Sub

So if you have more than one cell selected or none then it will exit.

If you want the change event to run when/if more than one cell is changed, we could use this. Target(1,1) is the 'active' cell in your selection. This forces Excel to only consider the active cell should more than one cell be changed. If you are not aware, there can only ever be 1 active cell in any selection and that is always the cell you start your selection from.

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If Target(1,1).AddressLocal = "$D$5" And Worksheets("Sheet4").Range("$F$5").Value <> Target(1,1).Value Then Worksheets("Sheet4").Range("$F$5").Value = Target(1,1).Value
End Sub

 

[figuare9]

Just wanted to say thank you again for the help! I ended up off of work for a while for a surgery, but I'm back and went through this thread again. It helped me immensely!! Sorry to bump an old post, but I had to say it..