Solved

# Return worksheet name where data is found on worksheet

Posted on 2013-11-26
Medium Priority
318 Views
I used Excel 2010 to figure the payroll commission that we pay our salespeople.

I have an individual worksheet for each person that totals in the same manner for everyone.

At the end, I have a page that totals all of the individual totals.

We use a "draw system" for paying our employees, which means that if they don't make a certain amount for this payroll period, we still pay them a base amount, but then they owe us back the difference on their next pay.

So, on each individual worksheet, there is a calculation for what the salespersons draw balance is, and like explained above, there is a total for this cell on the "totals" page.

I would like it to list somewhere on the totals page, which worksheets (or salespeople) have a draw balance of greater than \$0.00.

For an (small) example:

Draw balance
Salesperson (worksheet) A \$0.00
Salesperson (worksheet) B \$ 5.00
Salesperson (worksheet) C \$20.00

Totals worksheet draw balance = \$25.00

On the totals page I would like to see:

Salespersons (worksheets) that have a draw balance of greater than \$0.00
B \$ 5.00
C \$20.00
0
Question by:drozeveld
• 5
• 4
• 3

LVL 44

Expert Comment

ID: 39678288
One possible solution is to display all worksheets and apply a filter which hides zero balance lines. Click on Data - Filter menu option.
0

LVL 1

Author Comment

ID: 39678313
I have over twenty worksheets, I'm not sure that would be easy to see.  Thanks for the suggestion.
0

LVL 44

Expert Comment

ID: 39678358
I suppose the Totals page is just one and 20 worksheets means 20 salespersons. So each person must have one line at Totals page which will contain the necessary cumulated data and the Filter will just define which lines to display at the moment.

If you add a new salesperson then you need to add one line to the Totals page but the Filter remains unchanged.

Of course, to generate the Totals page by some VBA code would also be an option.
0

LVL 1

Author Comment

ID: 39678429
I'll attach my file here.  Please look at the "draw balances sheet" for what I am trying to do.  Hopefully this will explain it better.
Sample-Draw-Workbook.xlsx
0

LVL 23

Accepted Solution

NBVC earned 2000 total points
ID: 39678474
Create a list of all the sheetnames to look in somewhere in the Draw Balances sheet.  Then name that range, something like List (do this by selecting the list of names and enter List in the Name box to the left of the formula bar).

Then enter formula in sheet, in say B8:

=IFERROR(INDEX(List,SMALL(IF(COUNTIF(INDIRECT("'"&List&"'!F42"),">0")>0,ROW(List)-MIN(ROW(List))+1),ROWS(\$A\$1:\$A1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down

in adjacent cell, C8, get the values with:

=IF(B8="","",INDIRECT("'"&B8&"'!F42"))

copied down

See attached.
Copy-of-Sample-Draw-Workbook.xlsx
0

LVL 44

Expert Comment

ID: 39678496
Yes, I understand it. Attached is the XLSX file with Total page updated and filter applied.
Sample-Draw-Workbook.xlsx
0

LVL 1

Author Comment

ID: 39678617
aaHHH...the filter works great on the Draw Balances page!!
0

LVL 23

Expert Comment

ID: 39678651
Hi drozveld,

I am not sure if you applied your points as you intended?  You assigned the full 500 to me, which I do appreciate, but your final comment leads one to believe that you, in fact, liked the filter suggestion offered by pcelba.

if that is the case you misassigned the points, then click the button to alert moderators so that you can re-open the thread and re-assign the points, either all to your first choice or split between the 2 options.....
0

LVL 44

Expert Comment

ID: 39678654
OK, you have two correct solutions. I would just guess the filter is easier to understand.
0

LVL 1

Author Comment

ID: 39678769
Hello to pcelba & NB_VC,

In the original post, pcelba suggested to display all the worksheets and apply a filter to only show the lines that had a balance of more than zero, but, I have too many worksheets to show at one time, so that was not going to work.

Then, NB_VC, gave me formulas and an example of how to set it up.

I personally originally figured I would have to list out all of the names on a separate sheet and then display cell F42 onthe separate page for each salesperson in that list by clicking "=rg!f42."

When I opened the spreadsheet from NB_VC, I saw the listed "filter" and went - oh, I could do what I originally thought I would have to do, but only have it show me the filter out totals and then my list would be easy to read....so that is what I did.

I will reopen the thread and realign the points, because you both deserve half.  I'm sorry that I mislead anyone one this.  (Actually, NB_VC, I tried the formula in my original spreadsheet, because those quarterbacks really don't work for me - although I wish they did, and the formula didn't work - I missed the boat on that portion of the solution-sorry.  I couldn't find the original formula that you gave me in the spreadsheet to see where it was pulling from anywhere).
0

LVL 23

Expert Comment

ID: 39678815
The formulas are in B8 and C8 of the Draw balances sheet.  The formula in B8 is referencing the List of sheetnames listed in column M, and the list is named List.  If you select M1:M5, and look at top left corner, you should see the name List in the name box.
0

LVL 1

Author Comment

ID: 39678847
Once again, I apologize.  I had opened the file that pcelba had attached.  I see your formulas now.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
###### Suggested Courses
Course of the Month7 days, 5 hours left to enroll