Top 10 List with Reference to Sheet Name

[killerb81]

Ok, I've been at this for hours and I'm going a little crazy.

I have a workbook with many sheets - each sheet is a job code, eg. "F0169852"
Each sheet keeps track of that job's expenses, mainly hours and salary.

Here's what I want to do:
Have a sheet called "admin" with a simple 2 column list.
Column 1 would be a "hours" top 10 list. It would show in decreasing order the jobs with the most hours logged. Example:

Hours
456
400
350
200
etc...

That was the easy part, I used the LARGE function to choose from the same cell on a range of sheets. =LARGE('-:end'!AA41,1) - i repeated that 10 times changing the "1" at the end to 2,3,4, etc... got me so far?

Beside those hours I want another column entitled "Job" showing me what job code (sheet name) that number came from. This is the part I can't figure out for the life of me...

Anyone have any idea? Did I explain myself clearly enough?

PLEASE HELP!!!

Awesome idea.

Here's a dummy file.

[stanleydgromjr]

killerb81,

Welcome to the Excel Forum.


Using Index and Match with some additional columns that you could move to the right and/or hide.

Detach/open workbook Index Match with hidden columns - killerb81 - EF767078 - SDG10.xls


Someone should be able to supply a formula to return the worksheet name without the above additional overhead.

[SDruley]

See new example attached

[killerb81]

That looks great!

Although I'm not sure I totally understand it, I'll have to read up on those functions.

Here's another question though, let's say I add another job sheet (which I do all the time) - is there a way to dynamically update that list without having to add more code?

That's why I used the LARGE command, because I could draw on a range of sheets for data, if I added a new sheet it would automatically be incorporated.

Is that possible?

[killerb81]

In your example file you use all 11 sheets.. I only want 10 in that list... the top 10, of a potential 100's of sheets.

In my actual sheet I already have more than 50 sheets.