Rusty Excel Skills looking to build a 'summary' page to pull 'name' and 'score/total' from each tab.

[treym]

Been away from Excel for a number of years. I building some training rubric grading sheets. Each person will have 2 different rubrics x 90 people = 180 total rubric. I'd like a summary page where it will grab the 'name' and total score from each tab, so I don't have to reference them myself. Is this a macro thing or am i overthinking it? Additionally, I will need to split each person's 2 rubrics to their own file for forwarding to them. How would that be done? Thanks!

 

[Joe4]

Quite frankly, all things equal, what you are describing is a relational database and would work best in a relational database program like Microsoft Access.
That being said, I know many people do not have Microsfot Access or the skills in Microsoft Access to create something like that.
One option would be to use Power Query, which allows you to do database-like operations in Excel. There is a whole forum devoted to the Power Tools.

Other than that, you want want to look at matching formulas like XLOOKUP, VLOOKUP, and INDEX/MATCH to look up your values are bring them back to your summary sheet.
You can even use INDIRECT and/or INDEX functions to dynamically get the values, if the names of the sheets match the people (so you do not need to hard-code the sheet names in your formulas).
And of course, VBA is always an option too.

Some of the options available to you may be limited by which version of Excel you are using. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you require more assistance, much more specific details will be needed, i.e. what your data structure is, what you want the final result to look like, etc.

 

[treym]

Quite frankly, all things equal, what you are describing is a relational database and would work best in a relational database program like Microsoft Access.
That being said, I know many people do not have Microsfot Access or the skills in Microsoft Access to create something like that.
One option would be to use Power Query, which allows you to do database-like operations in Excel. There is a whole forum devoted to the Power Tools.

Other than that, you want want to look at matching formulas like XLOOKUP, VLOOKUP, and INDEX/MATCH to look up your values are bring them back to your summary sheet.
You can even use INDIRECT and/or INDEX functions to dynamically get the values, if the names of the sheets match the people (so you do not need to hard-code the sheet names in your formulas).
And of course, VBA is always an option too.

Some of the options available to you may be limited by which version of Excel you are using. I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you require more assistance, much more specific details will be needed, i.e. what your data structure is, what you want the final result to look like, etc.

Thanks, if these weren't 'rubrics' where there are lots of cells with skills and skill descriptions, then the total, I would create it more database friendly. But we have 'graders' that have to view all the skills and descriptions to know how to give them a score. I used PowerPivot and the like several years ago when you had to actually install it separately, so may try that again. I'm using a new version (Office 365) since its on my work computer and updated regularly, so shouldn't be any issues there.

 

[Joe4]

I'm using a new version (Office 365) since its on my work computer and updated regularly, so shouldn't be any issues there.

If you update your account details, as described in my initial reply, that will be very helpful to users on future questions you post here, as they will be able to see what version you are using at a glance, i.e.

 

[treym]

If you update your account details, as described in my initial reply, that will be very helpful to users on future questions you post here, as they will be able to see what version you are using at a glance, i.e.
View attachment 94447

Does that help?

 

[Joe4]

View attachment 94449 View attachment 94450Does that help?

No, I am suggesting that you update your account details, so that it always appears under your account information and icon that shows to the left of every post you make.
If you click on the Account Details link I provided in my first reply, it will take you right there.
Then just update those settings/value, and save.

If you do it right, you will see those details in that image, just like mine shows.

 

[treym]

No, I am suggesting that you update your account details, so that it always appears under your account information and icon that shows to the left of every post you make.
If you click on the Account Details link I provided in my first reply, it will take you right there.
Then just update those settings/value, and save.

If you do it right, you will see those details in that image, just like mine shows.
View attachment 94451

Sorry, misread that. I've updated my account stuff and saved it.

 

[treym]

So here is what my summary page (at its simplest) looks like, with my instructions/thoughts of how I want the formula to work. Essentially, the formal would take the 'name' to the far left, scan the worksheets for that same name, go to a cell in that identified worksheet and copy the cell contents back to the columns to the right of the name. In this case, there are two different types of Rubrics along the top, the individual names would run down the left and would match the names at the bottom of each worksheet. Tried index/match, but wasn't sure how to get that to reference tab names and not individual cells.

Sheet index:	A-NA Rubric Score	1-5 Rubric Score
Name1 TP	Look up name at left, match to worksheet name, pull cell J20 on that worksheet. This will match in B2, but blank in B3)	Look up name at left, match to worksheet name, pull cell k49 from that worksheet (this will be blank in C2, but filled in C3)

 

[Joe4]

I am a very visual person (for complex things, often times if I cannot see it, it doesn't make sense). And the details would be heavily dependent on how everything is laid out.
As such, it would be most helpful if you showed us some actual worksheet examples of the sheets with the data, and an example of how you want the summary sheet to look, so we can see how everything needs to be mapped.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[treym]

Thanks, I cannot get the plug in to work as it seems like the macro is being blocked somehow even though it is 'active' with full macros enabled.