Pull in data from store sheets into master ranking sheet

[Woodpusher147]

Hello
I hope someone can help with a task I need to complete

I have 4 sheets that represent 4 stores and need to pull 3 columns from these sheets depending on what department they state in column D.
Below is a snip of one of the stores

Column C = Staff Name
Column D = Department and is a dropdown list "Bedroom", "Upstairs" & "Downstairs"
Column E = Total points

I need to rank ALL staff in a separate sheet by department
e.g. grab all names and scores from Store 1, Store2 , Store3 and Store4 sheets that are on a row where column D contains "Bedroom" and then rank them by column E (points)
If it is possible, I would also like the rows to show WHICH store/sheet name the member of staff came from
This needs to be done for all 3 departments

I envisage the results to look like the below

The above could be on one sheet named "Bedroom" or all 3 departments could be ranked on one sheet called "Leaderboard" or something like.


I hope thats clear and thank you for any help

Simon

 

[Woodpusher147]

COuld really do with some help on this one please