Subhash wants to make changes in either the source or copied data and have it reflected in the other place. A little bit of VBA makes this possible.
The code from the video:
Private Sub Worksheet_Change(ByVal Target As Range)
MirrorCell = False
Select Case Target.Address
Case "$D$3"
MirrorCell = "B7"
Case "$E$3"
MirrorCell = "B8"
Case "$F$3"
MirrorCell = "B9"
Case "$G$3"
MirrorCell = "B10"
Case "$H$3"
MirrorCell = "B11"
Case "$B$7"
MirrorCell = "D3"
Case "$B$8"
MirrorCell = "E3"
Case "$B$9"
MirrorCell = "F3"
Case "$B$10"
MirrorCell = "G3"
Case "$B$11"
MirrorCell = "H3"
End Select
If MirrorCell = False Then Exit Sub
Application.EnableEvents = False
Range(MirrorCell).Value = Target.Value
Application.EnableEvents = True
End Sub
The code from the video:
Private Sub Worksheet_Change(ByVal Target As Range)
MirrorCell = False
Select Case Target.Address
Case "$D$3"
MirrorCell = "B7"
Case "$E$3"
MirrorCell = "B8"
Case "$F$3"
MirrorCell = "B9"
Case "$G$3"
MirrorCell = "B10"
Case "$H$3"
MirrorCell = "B11"
Case "$B$7"
MirrorCell = "D3"
Case "$B$8"
MirrorCell = "E3"
Case "$B$9"
MirrorCell = "F3"
Case "$B$10"
MirrorCell = "G3"
Case "$B$11"
MirrorCell = "H3"
End Select
If MirrorCell = False Then Exit Sub
Application.EnableEvents = False
Range(MirrorCell).Value = Target.Value
Application.EnableEvents = True
End Sub
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2422.
Can you make a change here and have it show up over there.
Or change it over there and have it show up here? Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen.
Today's question sent in by Subhash.
He was watching Link and Transpose and he noted that following the steps in the podcast.
Using the TRANSPOSE function, you can't update something here.
Right? That's an error. #SPILL!
error if you have dynamic arrays or “you can't change part of an array” in older versions of Excel.
And he said, is there a way that if I make any changes in the source it reflects and the result cells? Well, that always works of course.
No worries there.
But then the really hard 3 words and vice versa that means Subash wants to be able to change things here and have it reflect up there.
That's not the way Excel works, but it turns out there's a way to do it.
You have to be very careful to follow all eight of these steps.
If you don't follow these steps, it's not going to work.
Most Excel files are saved as XLSX. You have to save it as XLSM.
If you don't do that, you can't do it. And then Alt+T for Tom, M for Mary, S for Sam.
That gets you to the Trust Center and Macro Settings.
Usually it's set up here to disable without notification.
You need to move it down to the second one, so that way you can run macros.
It'll tell you every time and you say yes, I wrote this macro. I'll enable it.
Alright, and then take your original data here. We're going to copy.
We're not going to do the thing in this podcast.
We're just going to do a paste special and Transpose. So that just creates a snapshot of the data.
It's not live.
If I change something here, it won't change in the other place … yet. Alright then.
I am going to switch over to VBA, so that's Alt+F11.
That means that you're running Windows, or you're running a Mac.
This is not going to work in Excel Online, Excel on the iPhone, Excel on Android, Excel on Commodore 64.
In VBA you may not see the Project Explorer.
So you want to come up here to View, Project Explorer. Find the workbook you're working on.
Find the sheet that you're working on.
Right click and View Code and you get this big blank area over here. There are two dropdowns.
In the first dropdown, choose Worksheet. And then they choose Selection Change.
That's not the one you want. Go to the second dropdown and choose Change.
This is a tiny little macro that runs every time that you're going to change a cell.
Right now I'll paste this code down in the YouTube description below.
You are going to copy from there and paste to here.
Alright, so every time that someone makes a change of this worksheet, I'm going to set up a variable called Mirror Cell. I am going to set it equal to False.
And then Target. Target is passed by Excel.
That's the cell they just changed. I want to see what cell did they just change?
And in this case, your worksheet is going to definitely be different, so you're going to have to edit this.
If they change $D$3 (and you need the dollar signs there), then the cell that wants to reflect the same thing as D3 is B7.
I'm going to switch back to excel and we'll look at that.
So if they change D3, I want the same change to appear here in B7.
Alright, and then the next thing will be if they change E3. I want that to appear in B8.
Look for all this to work, it means that no one is going to insert cells or anything like that.
Alright. That took me 5 minutes. I only have 10 cells.
If you have 100 cells, it's going to take a little bit longer.
What this code says is: check to see what just changed. If it was D3, then we're going to mirror it in B7.
If it was $E$3, then mirror in B8 and so on. Going both one way and then the other.
And then down here if the mirror cell is still equal to False, that means they changed some other cell. We just exit the subroutine.
Otherwise we turn off the event handlers temporarily.
And then change the mirror cell to be the value that we just typed, and then turn the event handlers back on. Super important that you do those steps.
Alright, so here we are. Let's come back and we will test it.
I should be able to type something up here or so for banana I'll type 100 and it appears there.
If I come down here and say no change it to 200 and it appears up there.
If I type something else out here 123, no change, nothing wrong.
So I'm free to change anything else in the spreadsheet But when I change anything here, it will appear in the first place.
Alright, so while Excel doesn't normally offer” that if it changes here, it'll show up there or change it there and It'll show up here”.
Using this VBA, in very limited specific case you can make it happen.
To learn more about VBA, check out this book Excel VBA and Macros.
There's an edition for 2016, 2019, I think every Excel edition going back to 2003.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well, I want to thank Subash for sending that question in. I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
Can you make a change here and have it show up over there.
Or change it over there and have it show up here? Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen.
Today's question sent in by Subhash.
He was watching Link and Transpose and he noted that following the steps in the podcast.
Using the TRANSPOSE function, you can't update something here.
Right? That's an error. #SPILL!
error if you have dynamic arrays or “you can't change part of an array” in older versions of Excel.
And he said, is there a way that if I make any changes in the source it reflects and the result cells? Well, that always works of course.
No worries there.
But then the really hard 3 words and vice versa that means Subash wants to be able to change things here and have it reflect up there.
That's not the way Excel works, but it turns out there's a way to do it.
You have to be very careful to follow all eight of these steps.
If you don't follow these steps, it's not going to work.
Most Excel files are saved as XLSX. You have to save it as XLSM.
If you don't do that, you can't do it. And then Alt+T for Tom, M for Mary, S for Sam.
That gets you to the Trust Center and Macro Settings.
Usually it's set up here to disable without notification.
You need to move it down to the second one, so that way you can run macros.
It'll tell you every time and you say yes, I wrote this macro. I'll enable it.
Alright, and then take your original data here. We're going to copy.
We're not going to do the thing in this podcast.
We're just going to do a paste special and Transpose. So that just creates a snapshot of the data.
It's not live.
If I change something here, it won't change in the other place … yet. Alright then.
I am going to switch over to VBA, so that's Alt+F11.
That means that you're running Windows, or you're running a Mac.
This is not going to work in Excel Online, Excel on the iPhone, Excel on Android, Excel on Commodore 64.
In VBA you may not see the Project Explorer.
So you want to come up here to View, Project Explorer. Find the workbook you're working on.
Find the sheet that you're working on.
Right click and View Code and you get this big blank area over here. There are two dropdowns.
In the first dropdown, choose Worksheet. And then they choose Selection Change.
That's not the one you want. Go to the second dropdown and choose Change.
This is a tiny little macro that runs every time that you're going to change a cell.
Right now I'll paste this code down in the YouTube description below.
You are going to copy from there and paste to here.
Alright, so every time that someone makes a change of this worksheet, I'm going to set up a variable called Mirror Cell. I am going to set it equal to False.
And then Target. Target is passed by Excel.
That's the cell they just changed. I want to see what cell did they just change?
And in this case, your worksheet is going to definitely be different, so you're going to have to edit this.
If they change $D$3 (and you need the dollar signs there), then the cell that wants to reflect the same thing as D3 is B7.
I'm going to switch back to excel and we'll look at that.
So if they change D3, I want the same change to appear here in B7.
Alright, and then the next thing will be if they change E3. I want that to appear in B8.
Look for all this to work, it means that no one is going to insert cells or anything like that.
Alright. That took me 5 minutes. I only have 10 cells.
If you have 100 cells, it's going to take a little bit longer.
What this code says is: check to see what just changed. If it was D3, then we're going to mirror it in B7.
If it was $E$3, then mirror in B8 and so on. Going both one way and then the other.
And then down here if the mirror cell is still equal to False, that means they changed some other cell. We just exit the subroutine.
Otherwise we turn off the event handlers temporarily.
And then change the mirror cell to be the value that we just typed, and then turn the event handlers back on. Super important that you do those steps.
Alright, so here we are. Let's come back and we will test it.
I should be able to type something up here or so for banana I'll type 100 and it appears there.
If I come down here and say no change it to 200 and it appears up there.
If I type something else out here 123, no change, nothing wrong.
So I'm free to change anything else in the spreadsheet But when I change anything here, it will appear in the first place.
Alright, so while Excel doesn't normally offer” that if it changes here, it'll show up there or change it there and It'll show up here”.
Using this VBA, in very limited specific case you can make it happen.
To learn more about VBA, check out this book Excel VBA and Macros.
There's an edition for 2016, 2019, I think every Excel edition going back to 2003.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
Well, I want to thank Subash for sending that question in. I want to thank you for stopping by.
I'll see you next time for another netcast from MrExcel.
