How to make a copy of selected columns of a table in another sheet

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,676
Office Version
  1. 365
Platform
  1. Windows
I have a table of date that I want to process in several different ways. Some of these ways are very similar so I made a second table on the same sheet. In the example below, the table on the left (SchoolW1) compares a weighted average as computed by a UDF to a simple average. The table on the right (SchoolW2) shows that the weights (above the tables in yellow) do not need to add up to 1. They get the same result as long as they have the same relative values. I made the body of SchoolW2 equal to that of SchoolW1 by simply typing "=" in each of the top cells in SchoolW1 and then clicking on the corresponding cells in SchoolW1 and then copying down.

I have a couple of questions:
  1. Is there an easier or better way to do this?
  2. I tried to make another table on a different sheet using the same process, but it didn't work. Is there a way to do that?
  3. Most importantly, is there a way to create a multiple copies of some data that are all interconnected so that any changes made to any of them will be reflected in all of the others?
Thanks

Weighted Averages.xlsx
BCDEFGHIJKLMNOPQRSTUV
4Weights10%10%20%10%10%40%100%Weights22422820
5Student#1#2Mid Term#3#4Final ExamWeighted AverageSimple AverageΔStudent#1#2Mid Term#3#4Final ExamWeighted AverageSimple AverageΔ
6Amy100100100100100100100.00100.00=0.00Amy100100100100100100100.00100.00=0.00
7Joe7575100757510090.0083.33+6.67Joe7575100757510090.0083.33+6.67
8Molly88898591878988.1088.17-0.07Molly88898591878988.1088.17-0.07
9Bill100100751001007585.0091.67-6.67Bill100100751001007585.0091.67-6.67
10Ruth506070809010082.0075.00+7.00Ruth506070809010082.0075.00+7.00
11Pete095970969476.1063.67+12.43Pete095970969476.1063.67+12.43
12Mike100908070605068.0075.00-7.00Mike100908070605068.0075.00-7.00
School Exams Wtd
Cell Formulas
RangeFormula
I4I4=SUM(TestWeights)
T4T4=SUM(TestWeightsEqual)
I6:I12I6=wtdavg(SchoolW1[@['#1]:[Final Exam]],TestWeights)
J6:J12J6=SUM(SchoolW1[@['#1]:[Final Exam]])/COUNT(SchoolW1[@['#1]:[Final Exam]])
K6:K12,V6:V12K6=[@[Weighted Average]]-[@[Simple Average]]
N6:N12N6=SchoolW1[@['#1]]
O6:O12O6=SchoolW1[@['#2]]
P6:P12P6=SchoolW1[@[Mid Term]]
Q6:Q12Q6=SchoolW1[@['#3]]
R6:R12R6=SchoolW1[@['#4]]
S6:S12S6=SchoolW1[@[Final Exam]]
T6:T12T6=wtdavg(SchoolW2[@['#1]:[Final Exam]],TestWeightsEqual)
U6:U12U6=SUM(SchoolW2[@['#1]:[Final Exam]])/COUNT(SchoolW2[@['#1]:[Final Exam]])
Named Ranges
NameRefers ToCells
'School Exams Wtd'!TestWeights='School Exams Wtd'!$C$4:$H$4I4, I6:I12
'School Exams Wtd'!TestWeightsEqual='School Exams Wtd'!$N$4:$S$4T4, T6:T12
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
It is quite easy to do this usingthe worksheet change event. Assuming a workbook with Sheet1, Sheet2 and Sheet3 put this code in Sheet1:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:K12")) Is Nothing Then
Application.EnableEvents = False
 ad = Target.Address
  With Worksheets("Sheet2")
   .Range(ad) = Target
  End With
  With Worksheets("Sheet3")
   .Range(ad) = Target
  End With
End If
Application.EnableEvents = True
End Sub
this code in Sheet2;
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:K12")) Is Nothing Then
Application.EnableEvents = False
ad = Target.Address
  With Worksheets("Sheet1")
   .Range(ad) = Target
  End With
  With Worksheets("Sheet3")
   .Range(ad) = Target
  End With
End If
Application.EnableEvents = True

End Sub
and this code in Sheet3:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:K12")) Is Nothing Then
Application.EnableEvents = False
 ad = Target.Address
  With Worksheets("Sheet1")
   .Range(ad) = Target
  End With
  With Worksheets("Sheet2")
   .Range(ad) = Target
  End With
End If
Application.EnableEvents = True
End Sub
type or delete in any sheet and it gets copied to the others
If you have a lot of sheets to link you could use the workbook sheet change event and do a loop
 
Upvote 0
It is quite easy to do this using the worksheet change event.

Hmmm... I am concerned about the "B2:K12" range. What happens if I move the table to another location, horizontally and/or vertically? Do the tables on the other sheets have to be at the same location in that sheet? And can I have two or more tables on one sheet?

The tables are named, and the names are global. Is there a way, without code, that I can "name" part of one table and tell Excel that that part is to "be
shared" and then add it to as many other tables as I need and have then "linked" so that any changes to one affects all?

Or should I do this in a database application?
 
Upvote 0
Hello Jennifer, I have seen some of your previous posts and I know you don't want to hear this, but a single source of truth with Power Query over the top of it would seem a more robust solution ;)
 
Upvote 0
Hmmm... I am concerned about the "B2:K12" range. What happens if I move the table to another location, horizontally and/or vertically? Do the tables on the other sheets have to be at the same location in that sheet? And can I have two or more tables on one sheet?

The tables are named, and the names are global. Is there a way, without code, that I can "name" part of one table and tell Excel that that part is to "be
shared" and then add it to as many other tables as I need and have then "linked" so that any changes to one affects all?

Or should I do this in a database application?
The tables don't have to be in the same place they could moved you could do this in a number of ways the easiest is probably the offset function.
You certainly could copy the tables to the same sheet once again offset will do that.
You could also copy from one table column to another table column even with different names using the table names, ( if you provided the mapping of the names)
Basically the principle is the same for all of the them. I showed the basic simplest code, it could obviously get very complicated if you have got a complicated mapping.
Depending on how complicated your requirements are you might Alex's suggestion above simpler in the long run.
 
Upvote 0
Hello Jennifer, I have seen some of your previous posts and I know you don't want to hear this, but a single source of truth with Power Query over the top of it would seem a more robust solution ;)
It's not that I don't want to hear it. It's just that my tired old brain is terrified of having to cram another concept into the few remaining, grossly overworked brain cells that are still in more or less working order. I have been meaning to take a crack at Power Query, so I guess the time is now. ?

Do you know of a really good intro somewhere, like YouTube? Or a really good book with a digital (eg Kindle) version?

Thanks
 
Upvote 0
The tables don't have to be in the same place they could moved you could do this in a number of ways the easiest is probably the offset function.
You certainly could copy the tables to the same sheet once again offset will do that.
You could also copy from one table column to another table column even with different names using the table names, ( if you provided the mapping of the names)
Basically the principle is the same for all of the them. I showed the basic simplest code, it could obviously get very complicated if you have got a complicated mapping.
Depending on how complicated your requirements are you might Alex's suggestion above simpler in the long run.
I'll take a look at Power Query. Thanks.
 
Upvote 0
I think the most well known book is "Master Your Data with Power Query in Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow" by Ken Puls & Miguel Escobar. Amazon does have it in Kindle.
You can get Hard Copy and PDF and a link to Amazon on the Mr Excel site here:
Master Your Data with Power Query in Excel and Power BI

There is lots of PQ video content out there but they generally revolve around addressing a specific task using PQ. Udemy and some of the other learning platforms have some affordable courses.
 
Upvote 0
I think the most well known book is "Master Your Data with Power Query in Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow" by Ken Puls & Miguel Escobar. Amazon does have it in Kindle.
You can get Hard Copy and PDF and a link to Amazon on the Mr Excel site here:
Master Your Data with Power Query in Excel and Power BI

There is lots of PQ video content out there but they generally revolve around addressing a specific task using PQ. Udemy and some of the other learning platforms have some affordable courses.
Great. Now I have no more excuses. ???
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top