Cell Name change based on another cell BG Colour

[PeterOz]

Hi
I have a workbook with 10 sheets of players. E>G Week1 Game1 Week1 Game 2 etc.
I have two questions.
1) In the current game I use the large formula to change the background colour of the 1st 2nd & 3rd place.
E.g Tim has the highest score and the cell colour is light blue.
If another player gets more points then Tim as the game progresses the Light blue cell BG colour will change to that player.
This all works well.
What I am trying for is to have another cell change to the name of the person as the BG colour changes.
The pic attached should help make the question clearer.

2) When I click on a cell it will highlight the row and the column.
So I do not have to have to press F9 after choosing a new cell so the row and column will change I have on the first sheet Week 1 Game 1 VBA code
Application.Calculate.
Is it possible to have that one code work across all 10 sheets or do I have to add the Application.Calculate code to each individual sheet.

Cheers
Peteroz

 

[noveske]

Hi Noveske
Thanks for your input.
I tried the vba code and no luck.
I then tried putting it into a module and still no luck
Cheers
PeterOzView attachment 98170

You have to put your actual code there...

 

[PeterOz]

Hi Fluff
That formula worked. Thank you.
Is it possible to have the same come from the report sheet and show in the game sheet?
The report sheet shows a total of the ongoing games (pic in post#8)
What i am hoping for is to have in each game sheet the current 3 place leaders for that game and also show the current overall 3 place leaders (pic in post1).
I tried putting Report! in your formula hoping it would take the information from the report sheet.
=INDEX(SORTBY(Report!A5:A100,B5:B100,-1),SEQUENCE(3)). This gave the names as 1st Adnan, 2nd Van, 3rd Tim which does not match the points on the report sheet.
The next step I tried was I put your formula into the report sheet in column T and I was just going to say in Week1game1 AD2 = Report!V2.
However when I put your formula into the report Sheet and adjust the reference To Column A and B I get 3 names that do not have a score.

In the report sheet Column A & B have a formula( I do not think this should matter)
Column A has =IF(UNIQUE(Reference!B4:B35)=0,"",UNIQUE(Reference!B4:B35))
Column B has =IF(SUM(D5:M5)=0,"",SUM(D5:M5)).

I hope the above is not confusing.
Cheers
PeterOz

 

[Muhammad_Usman]

Hi Muhammed.
I hope I am not being a bother.
In the spreadsheet is a sheet report that shows the names an a total of each game and a grand total.
I wrongly assumed that if I put your formula next to the game formula it would show the current overall winner.
I added the sheet name Report and changed the cell reference.
By the result I get this is not correct.
is it possible to also show the first 3 places from the running total?
I put this in. IFNA(INDEX(Report!$A$5:$A$15,MATCH(LARGE(Report!$B$6:$B$15,ROW(B1)),Report!$B$6:$B$15,0)),"")
Cheers
PeterOzView attachment 98165

Sorry for late reply as last day was holiday .

Good try to get Result with running total . You almost done . Just have to minor changes

Try this one

IFNA(INDEX(Report!$A$5:$A$15,MATCH(LARGE(Report!$B$5:$B$15,ROW(B1)),Report!$B$5:$B$15,0)),"")

B$5 instead of B$6

 

[PeterOz]

Sorry for late reply as last day was holiday .

Not a problem -Hope it was a good day.
That worked thank you.
Cheers
PeterOz

 

[PeterOz]

Thank you all for the help.
For anyone else
The formula in post 2 and post 10 worked.
Cheers
PeterOz

 

[Muhammad_Usman]

Glas to He

Not a problem -Hope it was a good day.
That worked thank you.
Cheers
PeterOz
[/QU

Not a problem -Hope it was a good day.
That worked thank you.
Cheers
PeterOz

Glad to Help You

 

[PeterOz]

You have to put your actual code there...

The code is Application.Calculate
On Week1Game1
I just have
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

 

[Fluff]

You need to specify the sheet name for column B as well

=INDEX(SORTBY(Report!A5:A100,Report!B5:B100,-1),SEQUENCE(3))

 

[PeterOz]

Sorry for the late reply

You need to specify the sheet name for column B as well

It still gives the wrong names
Stacey, Peppe,Pat.
Cheers
PeterOz

 

[Fluff]

Do you have formulae in col B that return ""