Solved

Return worksheet name where data is found on worksheet

Posted on 2013-11-26
13
311 Views
Last Modified: 2013-11-29
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
Comment
Question by:drozeveld
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
13 Comments
 
LVL 42

Expert Comment

by:pcelba
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

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

Expert Comment

by:pcelba
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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 1

Author Comment

by:drozeveld
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

by:
NBVC earned 500 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 42

Expert Comment

by:pcelba
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

by:drozeveld
ID: 39678617
aaHHH...the filter works great on the Draw Balances page!!
0
 
LVL 23

Expert Comment

by:NBVC
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 42

Expert Comment

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

Author Comment

by:drozeveld
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

by:NBVC
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

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question