Way to move values based on another cell around like chess pieces

[SM mechanic]

So I don't know if this is even possible, or what to call it if it is. Maybe if someone could just direct me to what it might be called I can try and figure it out on my own. The task is to try to arrange technicians into teams based on their productions.

So I have a chart on a spreadsheet that tracks each tech's production. It has names in one column, next column is their daily production, next column multiplies daily production by 22 ( average work days in our month), next column is their weekly production, column next to that multiplies weekly by 4. Sometimes the daily x 22 is more realistic, sometimes weekly x 4 is more realistic depending on the way each tech works. So I calculate it each way.

I am looking for a way to be able to type in a techs name from column 1 and have the cell next to it automatically populate that techs average. I am trying to combine techs into teams based on each ones production so I can make each teams production match the needs I have for them. So I would like to be able to mix and match each tech and move them around to see how different combinations might produce.

For example I would like to type in Joe 45
Matt 51
Steve 54
Team total 150

But maybe I want a team that will average 155, so I take out Joe and type in Jeff, who averages 50, and the total changes to 155 and now I have my team.

So the inputs on the chart are dynamic, the daily and weekly number changes based on their production which I track daily, and I want to be able to type a techs name into another chart and have it fill in with their average pulled from the other chart.

I hope this makes sense

 

[myall_blues]

Something like this maybe. Put a name in the orange cells and it will populate the other columns. I was unsure what average you meant when you said "automatically populate that techs average" so I provided all 3 (monthly based on days, monthly based on weeks and the average of those two).

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Daily prod.	Monthly by day	Weekly prod	Monthly by week				Name	Monthly by day	Monthly by week	Average
2	Joe	5	110	24	96				Mitch	132	154	143
3	Suzie	6	132	31	124				Joe	110	110	110
4	Mitch	7	154	33	132				Steve	198	198	198
5	Matt	8	176	39	156
6	Steve	9	198	41	164
7
8
9
10									Total	440	462	451
11
Sheet1

Cell Formulas
Range	Formula
J2	J2	=IF($I2="","",VLOOKUP($I2,$A$2:$E$6,5,FALSE))
K2:K9	K2	=IF($I2="","",VLOOKUP($I2,$A$2:$E$6,3,FALSE))
L2:L9	L2	=IFERROR(AVERAGE(J2:K2),"")
J3:J9	J3	=IF(I3="","",VLOOKUP(I3,$A$2:$E$6,3,FALSE))
J10:L10	J10	=SUM(J2:J9)
C2:C6	C2	=B2*22
E2:E6	E2	=D2*4

 

[SM mechanic]

This looks great, I need to figure out how to put it into a sheet on my computer to try it out, because I can't read the formulas to see how it works. I will work on this today.
Thank you for the help.

 

[SM mechanic]

Took me a little bit of time to figure out how to put this into my sheet and reference the calls where they are in my sheet. But that is solely my lack of computer savviness in the end I figured it out. And it works perfectly.

That is amazing that you could take my rambling and interpret it, then turn that into perfectly working formulas.

Thank you very much myall_blues !!

 

[myall_blues]

You’re welcome. Thanks for the feedback.