I hate to keep asking for help, but I can't get this on my own. :-(

[TAPS_MikeDion]

Hi all,

I have a TOTALS sheet that needs to pull data from several sheets and I just can't seem to get this right. Sorry, I wish I could get this on my own, but I'm stumped. I'm thinking this is going to be a VBA project.

Here are the sheets in the workbook (table examples are below):

• EMPLOYEES
  • 2 Columns: First Name, Last Name
  • Up to 75 rows
• LOCRATE
  • 3 Columns: Location Name, Location Abbrev (3-letters), Pay Rate
  • # of rows are based on however many locations
• 17 (3-letter) Location sheets (i.e BGE, PAH, PDD, and so on)
  • Each location sheet has the employees names (linked to from the EMPLOYEES sheet)
  • 34 Columns: Last Name, First Name, WAL (Works At Location), 1-31 (Days of the month)
  • Up to 75 rows (# of employees)
• SUMMARY
• Employees names (linked to from the EMPLOYEES sheet)
  • 20 Columns: Last Name, First Name, Fixed Pay, 17 location sheet names
  • Up to 75 rows (# of employees)
• TOTALS
  • Here's where I'm having the trouble
  • Employees names (linked to from the EMPLOYEES sheet)
  • 8 Columns: Last Name (A), First Name (B), Location (C), Location Hourly Rate (D), Hours Worked (E), Total Hours Worked (F), Pay (G)
  • # of rows will always vary

In the TOTALS sheet:

• I need to search through all rows in the SUMMARYsheet...
  • Put the employee names from the SUMMARY sheet columns A & B in the TOTALS sheet columns A & B
  • Going down the rows in the SUMMARYsheet for each employee
    • Find every occurence of an hours total in a location column of the SUMMARY sheet
    • If hours were found in any location column
      • Look in the LOCRATE sheet for the "Location Name"
        • Find the matching the 3-letter code in LOCRATE column B that matches the SUMMARY sheet location column 3-letter name
        • Using that row in the LOCRATE sheet...
        • Put column A data (location name) in column C of the TOTALS sheet
        • Put column C data (Pay Rate) in column D of the TOTALS sheet
    • Put the hours worked total from the SUMMARY sheet into column E
    • When all SUMMARY sheet location columns (17 columns) have been gone through, then...
• Column F in the TOTALS sheet needs to total up all column E entries for that employee
• Column G then calculates the "Pay"...
  • Going down the rows for that employee
    • Multiply column D & column E amounts

Hopefully looking at the table3s makes it easier to follow.

EMPLOYEES sheet

LAST NAME	FIRST NAME
Doe	Jane
West	Tom
Gates	Bill

LOCRATE sheet

LOCATION NAME	LOCATION ABBREV	PAY RATE
Willy Wonka's Chocolate Factory	WWC	15
Joe's Window Wash	JWW	12

WWC (3-Letter Location) sheet (only went from day 1 to 5 to keep it short)
WAL = Works At Location
Employee 1 worked 24 hours at this location from the 1st to the 3rd of the month
Employee 2 does not work at this location
Employee 3 worked 16 hours at this location from the 4th to the 5th of the month

LAST NAME	FIRST NAME	WAL	1	2	3	4	5
=employee sheet A2	=employee sheet B2	x	8	8	8
=employee sheet A3	=employee sheet B3
=employee sheet A4	=employee sheet B4	x				8	8

JWW (3-Letter Location) sheet (only went from day 1 to 5 to keep it short)
WAL = Works At Location
Employee 1 does not work at this location
Employee 2 worked 24 hours at this location on the 1st, 3rd and 5th of the month
Employee 3 worked 16 hours at this location on the 2nd & 4th of the month

LAST NAME	FIRST NAME	WAL	1	2	3	4	5
=employee sheet A2	=employee sheet B2
=employee sheet A3	=employee sheet B3	x	8		8		8
=employee sheet A4	=employee sheet B4	x		8		8

SUMMARY sheet
For argument sake (Tom West - employee sheet A3) is on fixed pay - that can be disregarded in any code written.

LAST NAME	FIRST NAME	FIXED PAY	WWC	JWW	ETC
=employee sheet A2	=employee sheet B2		24
=employee sheet A3	=employee sheet B3	x		24
=employee sheet A4	=employee sheet B4		16	16

TOTALS sheet
Using the information pulled in from the other sheets of the workbook...
Columns F & G in the 4th line are just empty cells since the TOTAL HOURS WORKED & PAY are filled in at the bottom of Bill Gates' rows of info.

LAST NAME	FIRST NAME	LOCATION	LOCATION HOURLY RATE	HOURS WORKED	TOTAL HOURS WORKED	PAY
Doe	Jane	Willy Wonka's Chocolate Factory	15	24	24	$360.00
West	Tom	Joe's Window Wash	12	24	24	$288.00
Gates	Bill	Willy Wonka's Chocolate Factory	15	16	LEAVE BLANK - NO DATA	LEAVE BLANK - NO DATA
Gates	Bill	Joe's Window Wash	12	16	32	$432.00 (240+192)

Hopefully this is enough info and not too confusing for whomever agrees to help me out on this.

THANK YOU IN ADVANCE!!!

I know this is a lot to ask for, but I don't know enough about VBA to do this one on my own.

 

[DRSteele]

I have no idea how to do this in VBA. But when I see a host of tables like this, I think Power Query. You should be able to use PQ to load and transform all the data into the final table on your TOTALS sheet.

Also, anyone wanting to help with this would probably want some better sample data together with expected outcome. Can you provide more detailed sample data? It can be uploaded using XL2Bb [see signature below].

 

[hernantorres23]

Hi, If you can share this workbook, without data, I'm sure I can help you, I have create several Payroll sheets, very complicate with a big lot of customization.

Or put every sheet using add-in specifically for this and it can be found here XL2BB

 

[TAPS_MikeDion]

Sorry for the delay, it's been a busy weekend.

Having trouble with trying Power Query and XL2BB, because I'm running on a Mac (can't get the boss to buy me a PC instead.)
However, I'm going to try to share the workbook without data later today.

 

[TAPS_MikeDion]

Work delay... as soon as I can try sharing the workbook, I will post again.

Thanks!

 

[hernantorres23]

No problem, I'll be waiting your file

 

[TAPS_MikeDion]

Hi Mike,
I have the file in Dropbox, but from what I'm seeing I need your email address in order to share the file, unless I'm supposed to share it some other way on MrExcel.

 

[hernantorres23]

Hi put a link here, I download this inmediatly

 

[TAPS_MikeDion]

Hopefully this works...
Thank you again!

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[hernantorres23]

Ready