Copying Data To A New Sheet, Based On A Specific Week's Data

[jeremy466clark]

Hi There

I'm hoping someone can help me. I and some friends have a simple fun competition each week based on the Formula 1 season.

I won't bore you with the competition details, but on the "Main Data" tab shown in the attached 1st pic, I manually enter Points Scored by my friends each week for the race (in Columns C, G, K, O, S, W, AA, & AE), based on where the race is held (Rows B6:B29). I also manually enter the Week Number in Cell AJ1, which calculates the various various averages shown.

What I am trying to do is automatically copy the points from the "Main Data" tab (in Columns C, G, K, O, S, W, AA, & AE), purely for the most recent race / week, into the table on the "Points Summary" tab, Columns B15:B22, (See the 2nd pic attached) which I can then sort into numerical order so I have a graph showing purely for that week's points.

Is there a formula for this, or will it involve coding (which I know nothing about!)?

Any help is greatly appreciated.

Kind regards

Jeremy

"Main Data" Tab

"Points Summary" Tab

 

[Snakehips]

@jeremy466clark Assuming that the race number in Column A coincides with the Round number in AJ1 then see if the below helps.

Book3
	A	B	C
13
14	Name	This Week's Points
15	Jez	0
16	Jo	1
17	Paul	8
18	Sara	9
19	Phil	11
20	Graeme	15
21	Kate	17
22	Julia	18
23
Points Summary

Cell Formulas
Range	Formula
A15:B22	A15	=SORT(TRANSPOSE(CHOOSECOLS(CHOOSEROWS('Main data'!C3:AE29,1,'Main data'!AJ1+3),1,5,9,13,17,21,25,29)),2,1)
Dynamic array formulas.

 

[jeremy466clark]

Hi Snakehips
Thanks so much for the very quick reply.
Forgive me if my questions are stupid, but I have very little experience with Excel.
Do I type the formula you have shown exactly as you have shown it? Or am I supposed to enter column & row numbers to replace where you have shown CHOOSECOLS(CHOOSEROWS ? If so, which column & row numbers do I enter?
And my 2nd stupid question......... In what cells am I typing the formula?
My apologies again
Looking forward to hearing from you again
Kind regards
Jeremy

 

[Snakehips]

Jeremy, If I have interpreted your data correctly then enter the formula into A15 only on the Points Summary sheet.
The result will spill into the adjacent cells.
The formula ,as is, is using Excel functions CHOOSECOLS and CHOOSEROWS which are available in Excel 365.

My testing data below yields the result posted above.

Book3
	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	AC	AD	AE	AF	AG	AH	AI	AJ
1																																				12
2
3			Phil				Jo				Sara				Kate				Paul				Jez				Graeme				Julia
4
5
6	1		13				7				17				20				17				5				20				7
7	2		11				5				20				0				1				5				5				2
8	3		18				13				9				3				12				6				17				12
9	4		13				3				18				12				6				9				5				4
10	5		17				19				20				16				19				17				17				9
11	6		7				8				20				3				20				1				16				3
12	7		2				13				8				19				7				17				16				3
13	8		16				1				13				11				1				0				16				17
14	9		19				20				8				19				19				4				7				14
15	10		5				12				11				1				1				2				0				0
16	11		0				10				5				16				14				11				7				7
17	12		11				1				9				17				8				0				15				18
18	13
19	14
20	15
21	16
22	17
23	18
24	19
25	20
26	21
27	22
28
Main data

HTH

 

[jeremy466clark]

Oh wow!!!
Thanks so much, Snakehips.
Once I realised I had to delete all the previously entered figures and names in the weekly results table after entering your Formula in A15, it works like a dream.
Thanks again so much
Kind regards
Jeremy

 

[jeremy466clark]

Sorry to bother you again @Snakehips
Purely for my own knowledge, can you explain in laymans terms exactly what the =SORT(TRANSPOSE(CHOOSECOLS(CHOOSEROWS('Main data'!C3:AE29,1,'Main data'!AJ1+3),1,5,9,13,17,21,25,29)),2,1) formula is actioning. It's working brilliantly, but I don't understand how
Thanks again
Jeremy

 

[Snakehips]

Jeremy,
Your main data range is 'Main data'!C3:AE29 the name being in row 1 of that range and the races below, starting in row 4.
'Main data'!AJ1 holds the Round No. that corresponds with a Race No of interest. The Race No. of interest will be found in row Race No. + 3 of the main data or Round No. + 3.
Hence we know that AJ1 + 3 is the row (within the data range) of the race of interest.
Using race 12 as our example:

CHOOSEROWS('Main data'!C3:AE29,1,'Main data'!AJ1+3)

Extracts two rows only from the data range. Rows 1 (Names) and 15 (Race 12)
From that ^^

CHOOSECOLS(CHOOSEROWS('Main data'!C3:AE29,1,'Main data'!AJ1+3),1,5,9,13,17,21,25,29

extracts the first then every fourth column that will contain the names and their points for Race 12.
TRANSPOSE(^^^^) converts the resulting array into two columns.
SORT(^^^^,2,1). sorts the above by column 2 (Points) in ascending order.

It only requires the formula in one cell as the 365 functions used are dynamic and spill automatically.

If you put

=CHOOSEROWS('Main data'!C3:AE29,1,'Main data'!AJ1+3)

. in a spare cell that is not hindered to the right by other data you will see its sub result.
Similarly with

 =CHOOSECOLS(CHOOSEROWS('Main data'!C3:AE29,1,'Main data'!AJ1+3),1,5,9,13,17,21,25,29)

. etc

HTH