[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

Return worksheet name where data is found on worksheet

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
drozeveld
Asked:
drozeveld
  • 5
  • 4
  • 3
1 Solution
 
pcelbaCommented:
One possible solution is to display all worksheets and apply a filter which hides zero balance lines. Click on Data - Filter menu option.
0
 
drozeveldAuthor Commented:
I have over twenty worksheets, I'm not sure that would be easy to see.  Thanks for the suggestion.
0
 
pcelbaCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
drozeveldAuthor Commented:
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
 
NBVCCommented:
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
 
pcelbaCommented:
Yes, I understand it. Attached is the XLSX file with Total page updated and filter applied.
Sample-Draw-Workbook.xlsx
0
 
drozeveldAuthor Commented:
aaHHH...the filter works great on the Draw Balances page!!
0
 
NBVCCommented:
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
 
pcelbaCommented:
OK, you have two correct solutions. I would just guess the filter is easier to understand.
0
 
drozeveldAuthor Commented:
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
 
NBVCCommented:
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
 
drozeveldAuthor Commented:
Once again, I apologize.  I had opened the file that pcelba had attached.  I see your formulas now.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now