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

[JenniferMurphy]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4	Weights	10%	10%	20%	10%	10%	40%	100%				Weights	2	2	4	2	2	8	20
5	Student	#1	#2	Mid Term	#3	#4	Final Exam	Weighted Average	Simple Average	Δ		Student	#1	#2	Mid Term	#3	#4	Final Exam	Weighted Average	Simple Average	Δ
6	Amy	100	100	100	100	100	100	100.00	100.00	=0.00		Amy	100	100	100	100	100	100	100.00	100.00	=0.00
7	Joe	75	75	100	75	75	100	90.00	83.33	+6.67		Joe	75	75	100	75	75	100	90.00	83.33	+6.67
8	Molly	88	89	85	91	87	89	88.10	88.17	-0.07		Molly	88	89	85	91	87	89	88.10	88.17	-0.07
9	Bill	100	100	75	100	100	75	85.00	91.67	-6.67		Bill	100	100	75	100	100	75	85.00	91.67	-6.67
10	Ruth	50	60	70	80	90	100	82.00	75.00	+7.00		Ruth	50	60	70	80	90	100	82.00	75.00	+7.00
11	Pete	0	95	97	0	96	94	76.10	63.67	+12.43		Pete	0	95	97	0	96	94	76.10	63.67	+12.43
12	Mike	100	90	80	70	60	50	68.00	75.00	-7.00		Mike	100	90	80	70	60	50	68.00	75.00	-7.00
School Exams Wtd

Cell Formulas
Range	Formula
I4	I4	=SUM(TestWeights)
T4	T4	=SUM(TestWeightsEqual)
I6:I12	I6	=wtdavg(SchoolW1[@['#1]:[Final Exam]],TestWeights)
J6:J12	J6	=SUM(SchoolW1[@['#1]:[Final Exam]])/COUNT(SchoolW1[@['#1]:[Final Exam]])
K6:K12,V6:V12	K6	=[@[Weighted Average]]-[@[Simple Average]]
N6:N12	N6	=SchoolW1[@['#1]]
O6:O12	O6	=SchoolW1[@['#2]]
P6:P12	P6	=SchoolW1[@[Mid Term]]
Q6:Q12	Q6	=SchoolW1[@['#3]]
R6:R12	R6	=SchoolW1[@['#4]]
S6:S12	S6	=SchoolW1[@[Final Exam]]
T6:T12	T6	=wtdavg(SchoolW2[@['#1]:[Final Exam]],TestWeightsEqual)
U6:U12	U6	=SUM(SchoolW2[@['#1]:[Final Exam]])/COUNT(SchoolW2[@['#1]:[Final Exam]])

Named Ranges
Name	Refers To	Cells
'School Exams Wtd'!TestWeights	='School Exams Wtd'!$C$4:$H$4	I4, I6:I12
'School Exams Wtd'!TestWeightsEqual	='School Exams Wtd'!$N$4:$S$4	T4, T6:T12

 

[offthelip]

It is quite easy to do this usingthe worksheet change event. Assuming a workbook with Sheet1, Sheet2 and Sheet3 put this code in Sheet1:

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;

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:

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

 

[JenniferMurphy]

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?

 

[Alex Blakenburg]

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

 

[offthelip]

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.

 

[JenniferMurphy]

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

 

[JenniferMurphy]

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.

 

[Alex Blakenburg]

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.

 

[JenniferMurphy]

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. ???