Solved

Return worksheet name where data is found on worksheet

Posted on 2013-11-26
13
310 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
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…

733 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