Summarize on a single sheet details from all other sheets including the sheet names

skinnea

Board Regular
Joined
Mar 15, 2003
Messages
137
Office Version
  1. 365
Platform
  1. Windows
Hoping someone can help - I'm sure this is simple and I'm just dim!

I have a spreadsheet that records candidates (one per sheet) and the scores they achieve on 3 tests, each test consisting of multiple elements.

Each sheet will sum that candidates scores across the elements and give a total for each of the 3 tests.

On a summary sheet I want to show (in rows) the candidates' name, and the total score for each of the 3 tests. That's easy, i can just directly reference each of the cells on the individual sheets (eg =Sheet1!A1)

BUT I want to do two other slightly more complex things...

1 - I want to pull the sheet name into a cell and show that as part of the row for each candidate
2 - I'd love to automatically rank the candidates by putting the highest scorer at the top, lowest at the bottom etc


Does that start to go into the realms of VB...? Thanks in advance!
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Its all data model to me.
A query for Each Sheet. Append all the Queries to a single table in the Data Model.
Build a Pivot Table using Sorting by Value of Test Total.
 
Upvote 0

Forum statistics

Threads
1,223,907
Messages
6,175,300
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top