Auto Update a table

[divster27]

I've got what i think should be a simple request... probably a little bit of VB

I've been tasked with producing a spreadsheet that shows all the remaining fixtures in the football season - serious stuff this - and i'd like the 'League Table' to update as users enter their predictions.

So when someone enters a prediction against the fixture (next to the H or A) - the total points on the far right hand side will update. that's easy enough

I'd like the Team Position in the table to move in line with their total points.

So from the paste below, if i was to put a 3 next to the Watford A fixture, the total will update to 48 in the league table, and Arsenal would move up to position 4
I know sorting is a manual task, but can it easily be automated?

Arsenal

45

Wolves

46

Spurs

39

United

46

Chelsea

50

West Ham

42

position

Team

Points

Watford

A

Watford

H

Brighton

A

Watford

H

Arsenal

H

Wolves

H

3

Chelsea

50

Leicester

H

Arsenal

A

Arsenal

H

Man City

A

Leicester

H

Liverpool

A

4

United

46

Liverpool

H

West Ham

A

Leeds

A

Spurs

H

Burnley

A

Aston Villa

H

5

Wolves

46

Aston Villa

A

Crystal Palace

H

Everton

H

Liverpool

A

Newcastle

H

Spurs

A

6

Arsenal

45

Crystal Palace

A

Everton

A

Man Utd

A

Leicester

H

Norwich

A

Everton

H

7

West Ham

42

Brighton

H

Leeds

H

West Ham

H

Everton

A

Brentford

H

Brentford

A

8

Spurs

39

Southampton

A

Aston Villa

H

Newcastle

H

Norwich

H

Southampton

A

Burnley

H

Manchester United

H

Newcastle

A

Aston Villa

A

Arsenal

A

Leeds

A

Chelsea

A

West Ham United

A

Man City

H

Brighton

H

Brentford

H

West Ham

H

Arsenal

H

Leeds

H

Burnley

A

Brentford

A

Brighton

A

Everton

A

Norwich

A

Newcastle United

A

Brighton

H

Leicester

H

Chelsea

H

Wolves

H

Man City

H

Everton

H

Chelsea

A

Liverpool

A

Crystal Palace

A

Man Utd

A

Brighton

A

Tottenham Hotspur

A

Norwich

H

Burnley

H

Watford

H

Chelsea

A

Liverpool

A

Norwich

A

 

[trunten]

I'd personally have a helper table that just kept running totals (which you could hide), then you can use the sort function to show that in decending order (by points). like so:

Arsenal

45

Wolves

46

Spurs

39

United

46

Chelsea

50

West Ham

42

Helper Table

position

Team

Points

Team

Points

Watford

A

Watford

H

Brighton

A

Watford

H

Arsenal

H

Wolves

H

1

=SORT(X3:Y8,2,-1)

Arsenal

=SUM(C:C)

Leicester

H

Arsenal

A

Arsenal

H

Man City

A

Leicester

H

Liverpool

A

2

Wolves

=SUM(F:F)

Liverpool

H

West Ham

A

Leeds

A

Spurs

H

Burnley

A

Aston Villa

H

3

Spurs

=SUM(I:I)

Aston Villa

A

Crystal Palace

H

Everton

H

Liverpool

A

Newcastle

H

Spurs

A

4

United

=SUM(L:L)

Crystal Palace

A

Everton

A

Man Utd

A

Leicester

H

Norwich

A

Everton

H

5

Chelsea

=SUM(O:O)

Brighton

H

Leeds

H

West Ham

H

Everton

A

Brentford

H

Brentford

A

6

West Ham

=SUM(R:R)

Southampton

A

Aston Villa

H

Newcastle

H

Norwich

H

Southampton

A

Burnley

H

7

Manchester United

H

Newcastle

A

Aston Villa

A

Arsenal

A

Leeds

A

Chelsea

A

8

West Ham United

A

Man City

H

Brighton

H

Brentford

H

West Ham

H

Arsenal

H

Leeds

H

Burnley

A

Brentford

A

Brighton

A

Everton

A

Norwich

A

Newcastle United

A

Brighton

H

Leicester

H

Chelsea

H

Wolves

H

Man City

H

Everton

H

Chelsea

A

Liverpool

A

Crystal Palace

A

Man Utd

A

Brighton

A

Tottenham Hotspur

A

Norwich

H

Burnley

H

Watford

H

Chelsea

A

Liverpool

A

Norwich

A

That sort formula results in this table:

 

[Fluff]

@trunten the OP's profile shows they are using xl 2016 & therefore don't have the sort function.

 

[trunten]

?

similar approach though. try this instead ?

				Helper Table
position	Team	Points		Team	Points	Rank
1	=IFERROR(INDEX($X$3:$X$8,MATCH($T3,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T3,$Z$3:$Z$8,0)),"")		Arsenal	=SUM(C:C)	=COUNTIF($Y$3:$Y$8,">"&$Y3)+COUNTIF($Y$3:Y3,$Y3)
2	=IFERROR(INDEX($X$3:$X$8,MATCH($T4,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T4,$Z$3:$Z$8,0)),"")		Wolves	=SUM(F:F)	=COUNTIF($Y$3:$Y$8,">"&$Y4)+COUNTIF($Y$3:Y4,$Y4)
3	=IFERROR(INDEX($X$3:$X$8,MATCH($T5,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T5,$Z$3:$Z$8,0)),"")		Spurs	=SUM(I:I)	=COUNTIF($Y$3:$Y$8,">"&$Y5)+COUNTIF($Y$3:Y5,$Y5)
4	=IFERROR(INDEX($X$3:$X$8,MATCH($T6,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T6,$Z$3:$Z$8,0)),"")		United	=SUM(L:L)	=COUNTIF($Y$3:$Y$8,">"&$Y6)+COUNTIF($Y$3:Y6,$Y6)
5	=IFERROR(INDEX($X$3:$X$8,MATCH($T7,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T7,$Z$3:$Z$8,0)),"")		Chelsea	=SUM(O:O)	=COUNTIF($Y$3:$Y$8,">"&$Y7)+COUNTIF($Y$3:Y7,$Y7)
6	=IFERROR(INDEX($X$3:$X$8,MATCH($T8,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T8,$Z$3:$Z$8,0)),"")		West Ham	=SUM(R:R)	=COUNTIF($Y$3:$Y$8,">"&$Y8)+COUNTIF($Y$3:Y8,$Y8)
7	=IFERROR(INDEX($X$3:$X$8,MATCH($T9,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T9,$Z$3:$Z$8,0)),"")
8	=IFERROR(INDEX($X$3:$X$8,MATCH($T10,$Z$3:$Z$8,0)),"")	=IFERROR(INDEX($Y$3:$Y$8,MATCH($T10,$Z$3:$Z$8,0)),"")

 

[divster27]

thanks for your input - appreciated
trying to work out what the letters relate to, so i can update them on my table

 

[trunten]

my fixtures table ran from A1:R16

Sorted table T2:V10 (including headers)

Helper table X1:Z8 (including headers)

those formulas all work on that basis. so would need to be tweaked to reference the areas on your sheet. I'll ee if i can do a simplified eg that you can copy/paste into excel

 

[trunten]

This should paste nicely into a blank sheet (A1) so you can see what the formulas are doing Just need to then expand to your full fixtures table

Arsenal

45

Wolves

46

Helper Table

position

Team

Points

Team

Points

Rank

Watford

A

3

Watford

H

1

=IFERROR(INDEX($L:$L,MATCH($H4,$N:$N,0)),"")

=IFERROR(INDEX($M:$M,MATCH($H4,$N:$N,0)),"")

Arsenal

=SUM(C:C)

=COUNTIF($M:$M,">"&$M4)+COUNTIF($M$4:M4,$M4)

Leicester

H

1

Arsenal

A

2

=IFERROR(INDEX($L:$L,MATCH($H5,$N:$N,0)),"")

=IFERROR(INDEX($M:$M,MATCH($H5,$N:$N,0)),"")

Wolves

=SUM(F:F)

=COUNTIF($M:$M,">"&$M5)+COUNTIF($M$4:M5,$M5)

Liverpool

H

1

West Ham

A

Aston Villa

A

Crystal Palace

H

Crystal Palace

A

Everton

A

Brighton

H

Leeds

H

Southampton

A

Aston Villa

H

Manchester United

H

Newcastle

A

West Ham United

A

Man City

H

Leeds

H

Burnley

A

Newcastle United

A

Brighton

H

Everton

H

Chelsea

A

Tottenham Hotspur

A

Norwich

H

Chelsea

A

Liverpool

A

 

[divster27]

BRILLIANT - thank you!