Solved

Return worksheet name where data is found on worksheet

Posted on 2013-11-26
13
305 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
  • 5
  • 4
  • 3
13 Comments
 
LVL 41

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 41

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
 
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 41

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 41

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now