+ Reply to Thread
Results 1 to 6 of 6

I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

  1. 02-02-2023, 04:55 PM #1
    petrodataJAMES
    petrodataJAMES is offline
    Forum Contributor
    Join Date
    08-14-2022
    Location
    San Luis Obispo, CA
    MS-Off Ver
    Microsoft 365 App for enterprise
    Posts
    181

    I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

    This Payroll worksheet takes its data from the tab 'Instructor Pay Data' and compares details from the tabs 'Instructor and Location Payrate' and 'Master Pay Rates'. I see three formulas on the tab "Instructor Pay Data' that are not functioning.
    column 'X' =IF(A2<>"",VLOOKUP(E2&A2,'Instructor and Location Payrate'!$A$1:$D$59995,4,FALSE),"-")
    column 'Y' =A2&X2&IF(J2="MEGA All Levels",J2,"")&W2
    column 'Z' =IFERROR(IF(W2<>"0",VLOOKUP(Y2,'Master Pay Rates'!$A$11:$C$2000,3,FALSE),"-"),"-")
    The instructions to run this sheet are vague but are on the tab 'Instructions'. The total payroll values should populate the tab 'Instructor Summary' PivotTable

    If anyone can assist here, it would be appreciated and can be profitable too.

    Thanks
    Last edited by AliGW; 09-12-2023 at 02:56 AM. Reason: Sensitive data removed at OP's request.
    Reply With QuoteRegister To Reply
  2. 02-02-2023, 05:11 PM #2
    JohnTopley
    JohnTopley is online now
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,192

    Re: I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

    Column Y

    =X2&W2
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  3. 02-02-2023, 05:32 PM #3
    thenewkidd
    thenewkidd is offline
    Forum Contributor
    Join Date
    05-03-2021
    Location
    Nashville
    MS-Off Ver
    MS Office 365
    Posts
    184

    Re: I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

    Number 1, The look up code in Column Y doesn't make sense.
    Not every row uses the same formula
    one row will say
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    While another row will say
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    This looks like someone went through and wanted to eliminate variables that were there by design to create a unique lookup.

    You will have to explain why every single row has a unique hard qualifier.
    There is nothing uniform about this workbook, the formulas don't make sense.

    Your Y1 says
    Asbury ParkSenior Instructor One Asbury Park MEGA All Levels1

    But for Z to pick up any pay rate associated to that, that exact name needs to exist in the Master Pay Rates Page.
    The best you have is
    Senior Instructor One Asbury Park MEGA All Levels1

    but Asbury ParkSenior Instructor One Asbury Park MEGA All Levels1 character by character does not match Senior Instructor One Asbury Park MEGA All Levels1

    This is probably what is causing the issues.
    Reply With QuoteRegister To Reply
  4. 02-02-2023, 06:04 PM #4
    petrodataJAMES
    petrodataJAMES is offline
    Forum Contributor
    Join Date
    08-14-2022
    Location
    San Luis Obispo, CA
    MS-Off Ver
    Microsoft 365 App for enterprise
    Posts
    181

    Re: I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

    That is great! I have data now.
    May I ask if this is possible, I have a couple of questions.
    1.) tab 'Instructor Summary' has a Pivot Table and the By Studio table has the Location Asbury Park and (blank). Can we make whatever locations appear here including (blank), add to the tab "Instructor and Location Payrate', column 'Z' Location List?
    2.) tab 'Master Pay Rates' has a list from column 'A' Payrates Overview, all those pay rates starting at 'A3' through 'A9' if there is a pay rate there, add to the tab "Instructor and Location Payrate', column 'AA' Location List?

    I am looking for some automation here. LMK.

    Thanks
    Reply With QuoteRegister To Reply
  5. 02-05-2023, 10:08 PM #5
    petrodataJAMES
    petrodataJAMES is offline
    Forum Contributor
    Join Date
    08-14-2022
    Location
    San Luis Obispo, CA
    MS-Off Ver
    Microsoft 365 App for enterprise
    Posts
    181

    Re: I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

    Can I get another quick look at this payroll template? I have added another location and several more pay types. I am confused about how to edit the VLOOKUP to look at the increased range.
    The tab 'Instructor and Location Payrate' column 'C' only has one of two locations on the pulldown. I have added the locations to column 'Z'. How do I increase this VLOOKUP?
    The tab 'Instructor and Location Payrate' column 'D' only has one of five Status on the pulldown. I have added the Status to column 'AA'. How do I increase this VLOOKUP?
    The tab 'Instructor Summary' column 'X' has this formula =IF(A2<>"",VLOOKUP(E2&A2,'Instructor and Location Payrate'!$A$1:$D$60003,4,FALSE),"-") can you screenshot where this formula is looking?
    The tab 'Instructor Summary' column 'Y' has this formula =A2&X2&IF(J2="MEGA All LeveMEGA All Levels",J2,"")&W2 can you screenshot where this formula is looking?

    Thanks
    Last edited by AliGW; 09-12-2023 at 02:57 AM. Reason: Sensitive data removed at OP's request.
    Reply With QuoteRegister To Reply
  6. 02-06-2023, 07:06 AM #6
    josephteh
    josephteh is offline
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,057

    Re: I will pay if someone can make this Payroll Worksheet function (this is a 911 for me).

    I have created a Lookup Tables sheet with 2 lookup tables and 2 columns in Instructor Pay Data sheet: Status in column Z and Pay Rate in column AA.
    Please Login or Register  to view this content.
    Last edited by AliGW; 09-12-2023 at 02:57 AM. Reason: Sensitive data removed at OP's request.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Trying to make a payroll form that divides reg time and ot after 8 and after 44 hours
    By CyrilSneer in forum Excel General
    Replies: 16
    Last Post: 10-18-2016, 02:10 PM
  2. How to make a reference of an another worksheet in the current worksheet using a function?
    By subbiralim in forum Excel General
    Replies: 1
    Last Post: 04-02-2015, 11:46 AM
  3. [SOLVED] How to make the Worksheet search function look at the next worksheet if user click no
    By Faridwahidi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-12-2014, 09:10 PM
  4. Payroll worksheet help
    By podge2010 in forum Excel General
    Replies: 2
    Last Post: 10-07-2011, 06:26 AM
  5. Excel 2007 : Payroll Worksheet
    By Kmac in forum Excel General
    Replies: 2
    Last Post: 04-02-2010, 02:44 PM
  6. how to make sure which worksheet call the function
    By lvcha.gouqizi in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-21-2005, 04:05 PM
  7. how do i make overtime in my payroll sheet calculate after 42.5 h.
    By Kaye in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-01-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1