copying cell value from columns that will change

[jbrown021286]

Tru Video Compliance report 12.23.csv
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
1	Technician	12/17 Uploaded	12/17 Sent	12/17 Viewed	12/18 Uploaded	12/18 Sent	12/18 Viewed	12/19 Uploaded	12/19 Sent	12/19 Viewed	12/20 Uploaded	12/20 Sent	12/20 Viewed	12/21 Uploaded	12/21 Sent	12/21 Viewed	12/22 Uploaded	12/22 Sent	12/22 Viewed	12/23 Uploaded	12/23 Sent	12/23 Viewed	12/24 Uploaded	12/24 Sent	12/24 Viewed	Total Uploaded	Total Sent	Total Viewed
2
3	Gustavo Alcaraz
4	Carl Alexander	4	4	4	4	4	3	4	4	4	2	2	2	4	4	4										18	18	17
5	Zirie Benton
6	Quenya Bogle	13	13	8	11	9	6	6	6	5	12	12	9	16	14	12										58	54	40
7	Jemira Brittain
8	Jeffrey Brown	2	2	1	1	1	1	3	3	2	4	4	3							7	7	6				17	17	13
9	Brandun Browne	3	3	2	4	4	3				1	1	1	3	3	1				4	4	4				15	15	11
10	CHRIS BURTON	5	5	4	5	5	2				3	3	2	3	2	1										16	15	9
11	JUSTIN CALKINS
12	TIONA CALLAWAY							4	4	4				9	9	5										13	13	9
13	Israel Cardona				3	3	2	4	4	4	1	1	1	3	3	2				4	4	4				15	15	13
14	Aaron Cromer
15	Ciera Farrow	7	7	7				5	5	4	8	8	7	2	2	1				4	4	4				26	26	23
16	Scott Ferrell
17	Jesse Gentle
18	KEDRIN GIBBY	6	6	6	1	1	0	2	2	2				3	3	3				5	5	4				17	17	15
19	Alexander Gomez
20	Jamar Hassan	10	10	9				5	5	3	12	11	9							6	6	5				33	32	26
21	PETER HEM	4	4	3	1	1	1				5	5	4							3	3	3				13	13	11
22	BILLY HUMMEL
23	BRAYAN JIMENEZ	2	2	2				4	4	2				1	1	1				1	1	1				8	8	6
24	Lindsey Kniesly
25	Justin Lewis				3	3	2							5	5	4										8	8	6
26	Garrett Maddox				1	1	1	4	4	2	2	2	2							6	6	5				13	13	10
27	Jake Maddox
28	Venro Mayo	1	0	0	2	0	0	3	0	0	2	0	0	3	0	0										11	0	0
29	BRANDON MCGHEE
30	Terence Mckibben
31	Jada Moore	12	12	8	8	8	5	5	5	4				7	7	6				12	12	8				44	44	31
32	Dylan Murphy
33	Alex Oliveros
34	Angel Ortiz				1	1	1	2	2	1	2	2	0	2	2	2				3	3	3				10	10	7
35	Ramy Oum	4	4	4	2	2	2	3	3	0	5	5	4													14	14	10
36	TRE REDD	2	2	2	5	5	3	2	2	2	3	3	3													12	12	10
37	Kirsten Reid
38	Ronnell Rhodes
39	Joshua Shreve				4	4	2	6	6	6	5	5	3	8	8	6				7	7	4				30	30	21
40	EVAN SPIKES
41	DANNY STATON
42	Gerardo Torres	3	3	3	0	1	0	5	5	3	5	5	4	5	5	4				7	7	5				25	26	19
43	Jonathan Tuzzo				4	4	3	4	4	3	2	2	1							5	5	4				15	15	11
44	Aldo Vaquera
45	Aldo Vaquera
46	aldo vaquera	2	2	2				2	2	1	2	2	2	5	5	4				4	4	4				15	15	13
47	Robert Walker
48	Jenn Wall
49	Total	80	79	65	60	57	37	73	70	52	76	73	57	81	73	56				78	78	64				448	430	331
Tru Video Compliance report 12.

Tru Video Compliance report 12.23.csv
	A	B	C	D	E
1
2		Tech	RO's	Videos	%
3		Gustavo Alcaraz
4		Carl Alexander
5		Zirie Benton
6		Quenya Bogle
7		Jemira Brittain
8		Jeffrey Brown
9		Brandun Browne
10		CHRIS BURTON
11		JUSTIN CALKINS
12		TIONA CALLAWAY
13		Israel Cardona
14		Aaron Cromer
15		Ciera Farrow
16		Scott Ferrell
17		Jesse Gentle
18		KEDRIN GIBBY
19		Alexander Gomez
20		Jamar Hassan
21		PETER HEM
22		BILLY HUMMEL
23		BRAYAN JIMENEZ
24		Lindsey Kniesly
25		Justin Lewis
26		Garrett Maddox
27		Jake Maddox
28		Venro Mayo
29		BRANDON MCGHEE
30		Terence Mckibben
31		Jada Moore
32		Dylan Murphy
33		Alex Oliveros
34		Angel Ortiz
35		Ramy Oum
36		TRE REDD
37		Kirsten Reid
38		Ronnell Rhodes
39		Joshua Shreve
40		EVAN SPIKES
41		DANNY STATON
42		Gerardo Torres
43		Jonathan Tuzzo
44		Aldo Vaquera
45		Aldo Vaquera
46		aldo vaquera
47		Robert Walker
48		Jenn Wall
Proformance

Cell Formulas
Range	Formula
B3:B48	B3	='Tru Video Compliance report 12.'!A3

i need to copy cell values from 1 sheet to another the tricky part is that the raw data is from a report that will add columns to the source sheet depending on how many days are in the report. the data needed will always be in the 2nd to last and 3rd to last columns of the Tru Video Compliance report 12. sheet . what i am wanting to do is copy the value of the 3rd to last column of the matching employee to column C in the table on the performance sheet and the 2nd to last column to column D so i can use them to get a % value In column E. is there a good way to do this since the column will change from day to day?

 

[alansidman]

I think that to truly understand what you want, we need to see a mocked up solution and not a sheet with empty cells. Help us to help you.

 

[jbrown021286]

I think that to truly understand what you want, we need to see a mocked up solution and not a sheet with empty cells. Help us to help you.

Tru Video Compliance report 12.23.csv
	A	B	C	D	E
1
2		Tech	RO's	Videos	%
3		Gustavo Alcaraz	18	18	100.00%
4		Carl Alexander			#DIV/0!
5		Zirie Benton	58	54	93.10%
6		Quenya Bogle			#DIV/0!
7		Jemira Brittain	17	17	100.00%
8		Jeffrey Brown	15	15	100.00%
9		Brandun Browne	16	15	93.75%
10		CHRIS BURTON			#DIV/0!
11		JUSTIN CALKINS	13	13	100.00%
12		TIONA CALLAWAY	15	15	100.00%
13		Israel Cardona			#DIV/0!
14		Aaron Cromer	26	26	100.00%
15		Ciera Farrow			#DIV/0!
16		Scott Ferrell			#DIV/0!
17		Jesse Gentle	17	17	100.00%
18		KEDRIN GIBBY			#DIV/0!
19		Alexander Gomez	33	32	96.97%
20		Jamar Hassan	13	13	100.00%
21		PETER HEM			#DIV/0!
22		BILLY HUMMEL	8	8	100.00%
23		BRAYAN JIMENEZ			#DIV/0!
24		Lindsey Kniesly	8	8	100.00%
25		Justin Lewis	13	13	100.00%
26		Garrett Maddox			#DIV/0!
27		Jake Maddox	11	0	0.00%
28		Venro Mayo			#DIV/0!
29		BRANDON MCGHEE			#DIV/0!
30		Terence Mckibben	44	44	100.00%
31		Jada Moore			#DIV/0!
32		Dylan Murphy			#DIV/0!
33		Alex Oliveros	10	10	100.00%
34		Angel Ortiz	14	14	100.00%
35		Ramy Oum	12	12	100.00%
36		TRE REDD			#DIV/0!
37		Kirsten Reid			#DIV/0!
38		Ronnell Rhodes	30	30	100.00%
39		Joshua Shreve			#DIV/0!
40		EVAN SPIKES			#DIV/0!
41		DANNY STATON	25	26	104.00%
42		Gerardo Torres	15	15	100.00%
43		Jonathan Tuzzo			#DIV/0!
44		Aldo Vaquera			#DIV/0!
45		Aldo Vaquera	15	15	100.00%
46		aldo vaquera			#DIV/0!
47		Robert Walker			#DIV/0!
48		Jenn Wall			#DIV/0!
Performance

Cell Formulas
Range	Formula
B3:B48	B3	='Tru Video Compliance report 12.'!A3
E3:E48	E3	=[@Videos]/[@[RO''s]]

in this example Column C data is from Column Z in the Tru Video Compliance report 12. sheet. and D is from AA. but those data source columns will change with the number of days in the report they will just always be the 2nd and 3rd to last columns. does that help?

 

[alansidman]

Here is a power query solution that worked for me when I added additional days.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Technician] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Technician", "Total Uploaded", "Total Sent"}),
    #"Inserted Division" = Table.AddColumn(#"Removed Other Columns", "Division", each [Total Sent] / [Total Uploaded], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", Percentage.Type}})
in
    #"Changed Type"

 

[jbrown021286]

Here is a power query solution that worked for me when I added additional days.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Technician] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Technician", "Total Uploaded", "Total Sent"}),
    #"Inserted Division" = Table.AddColumn(#"Removed Other Columns", "Division", each [Total Sent] / [Total Uploaded], type number),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Division",{{"Division", Percentage.Type}})
in
    #"Changed Type"
I'm not sure i follow what i need to do with this since it isn't a formula to put in a cell. could you walk me through what i need to do. i don't have any experience with "power query"
[/QUOTE]