Okay Here is a solution. It will require some work on your part
The solution will involve you copying and pasting information that is calculated below your paystub replica into a summary worksheet that has a table of the paystub line items. Below are the paystub replication and the summary worksheet.
This next step is not necessary, but it will help ensure you're date and replica paystub are matching:
(If you don't want this, then delete cell E55, and remove conditional formatting from cells B55:E55.)
You'll need to copy this custom function into a module in your VBA editor.
Press ALT-11, Open the Project Explorer (if it is not already open from the menu or Cntl-R),
Find VBA Project ("your workbook name"), right click, Click the arrow beside "Insert" then MODULE.
You should see a Module open under that line in the Project Explorer pane.
Select that module, double click or Press F7, it will take you to the code window for that module.
Paste this into the Module:
Function ThisSheetName() As Variant
Dim sn As Variant
sn = ActiveSheet.Name
ThisSheetName = sn
End Function
If you have more than 8 possible line items you'll need to update the formulas that feed the "copy" grid area AND move that entire section so it will not be affected by new rows.
In the summary worksheet you must convert your data to a TABLE (Cntrl T).
Paste the data onto the summary worksheet in the first row under the table.
The format of the pasted data should match the table's formatting.
You can now make pivot tables and all other summarizations based on all of your data.
Mr Excel Questions2.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J |
---|
13 | Earnings | | | | | | Rate | Hours | This Period | YTD |
---|
14 | Taxable Tip Hours | | | | | | 32 | 31.36 | | |
---|
15 | Stand In Hours | | | | | | 15 | 34.32 | 514.8 | |
---|
16 | Regular Hours | | | | | | 11.75 | 32 | 376 | |
---|
17 | Overtime Pay Rate | | | | | | 17.63 | - | - | |
---|
18 | PTO Hours | | | | | | 11.75 | - | - | |
---|
19 | Trainning | | | | | | - | - | - | |
---|
20 | Holiday Pay | | | | | | - | - | - | |
---|
21 | Incentive | | | | | | - | - | - | |
---|
22 | TOTAL GROSS PAY: | | | | | | | | 890.8 | |
---|
23 | Per Taxes | | | | | | | | This Period | YTD |
---|
24 | Dental Insurance | | | | | | | | 21 | |
---|
25 | Health Insurance | | | | | | | | 31 | |
---|
26 | 401K | | | | | | | | 92.22 | |
---|
27 | TOTAL PRE TAXES: | | | | | | | | 144.22 | |
---|
28 | FEDERAL WITHHOLDINGS | | | | | | | | This Period | YTD |
---|
29 | Federal Income Tax Withholding | | | | | | | | 60.66 | |
---|
30 | Louisiana State Income Tax Withholdings | | | | | | | | 19.3 | |
---|
31 | Medicare Tax Withholdings | | | | | | | | 12.62 | |
---|
32 | Social Security Tax Withholdings | | | | | | | | 53.95 | |
---|
33 | TOTAL FEDERAL AND STATE WITHHOLDING: | | | | | | | | 146.53 | |
---|
34 | AFTER TAXES Deductions | | | | | | | | This Period | YTD |
---|
35 | Universal Life Insruance | | | | | | | | 14.79 | |
---|
36 | 402 K Loan #9 | | | | | | | | 108.56 | |
---|
37 | 402 K Loan #8 | | | | | | | | 174.31 | |
---|
38 | TOTAL AFTER TAXES DEDUCTIONS: | | | | | | | | 297.66 | |
---|
39 | NET PAY: | | | | | | | | This Period | YTD |
---|
40 | Total Net Pay | | | | | | | | 302.39 | |
---|
41 | Other Benefits & Information | | | | | | | | This Period | YTD |
---|
42 | PTO Hours Available | | | | | | | | | |
---|
43 | | | | | | | | | | |
---|
44 | Pay Summary | | | | | | | | This Period | YTD |
---|
45 | Earnings | | | | | | | | 890.8 | |
---|
46 | Pre-Tax Deductions | | | | | | | | 144.22 | |
---|
47 | Federal Taxable Wages | | | | | | | | 749.72 | |
---|
48 | Social Security Taxable Wages | | | | | | | | 838.8 | |
---|
49 | Medicare (HI) Taxable Wages | | | | | | | | 838.8 | |
---|
50 | State Taxable Wages | | | | | | | | 749.72 | |
---|
51 | Total Taxes | | | | | | | | 146.53 | |
---|
52 | After-Tax Deductions | | | | | | | | 297.66 | |
---|
53 | Net Pay | | | | | | | | 302.39 | |
---|
54 | PayPeriod as a Date | | | | | | | | | |
---|
55 | | | | | | | | | | |
---|
56 | | | | | | | | | | |
---|
57 | Copy this section as values into the summary workbook in the first row under the table: | | | | | | | | | |
---|
58 | With Some More Recent, Non 365 Versions | | | | With 365 | | | | | |
---|
59 | | | | | | | | | | |
---|
60 | | | | | | | | | | |
---|
61 | | | | | | | | | | |
---|
62 | | | | | | | | | | |
---|
63 | | | | | | | | | | |
---|
64 | | | | | | | | | | |
---|
65 | | | | | | | | | | |
---|
66 | | | | | | | | | | |
---|
67 | | | | | | | | | | |
---|
68 | | | | | | | | | | |
---|
69 | | | | | | | | | | |
---|
70 | | | | | | | | | | |
---|
|
---|
Mr Excel Questions2.xlsm |
---|
|
---|
| A | B | C |
---|
1 | Line Item | Pay Period | Amount |
---|
2 | After-Tax Deductions | 11/20/2022 | 250 |
---|
3 | Earnings | 11/20/2022 | 950 |
---|
4 | Federal Taxable Wages | 11/20/2022 | 950 |
---|
5 | Medicare (HI) Taxable Wages | 11/20/2022 | 950 |
---|
6 | Pre-Tax Deductions | 11/20/2022 | 175 |
---|
7 | Social Security Taxable Wages | 11/20/2022 | 950 |
---|
8 | State Taxable Wages | 11/20/2022 | 950 |
---|
9 | Total Taxes | 11/20/2022 | 150 |
---|
10 | After-Tax Deductions | 12/4/2022 | 275 |
---|
11 | Earnings | 12/4/2022 | 750 |
---|
12 | Federal Taxable Wages | 12/4/2022 | 750 |
---|
13 | Medicare (HI) Taxable Wages | 12/4/2022 | 750 |
---|
14 | Pre-Tax Deductions | 12/4/2022 | 150 |
---|
15 | Social Security Taxable Wages | 12/4/2022 | 750 |
---|
16 | State Taxable Wages | 12/4/2022 | 750 |
---|
17 | Total Taxes | 12/4/2022 | 165 |
---|
18 | After-Tax Deductions | 12/18/2022 | 295 |
---|
19 | Earnings | 12/18/2022 | 1100 |
---|
20 | Federal Taxable Wages | 12/18/2022 | 1100 |
---|
21 | Medicare (HI) Taxable Wages | 12/18/2022 | 1100 |
---|
22 | Pre-Tax Deductions | 12/18/2022 | 165 |
---|
23 | Social Security Taxable Wages | 12/18/2022 | 1100 |
---|
24 | State Taxable Wages | 12/18/2022 | 1100 |
---|
25 | Total Taxes | 12/18/2022 | 170 |
---|
26 | After-Tax Deductions | 1/1/2023 | 297.66 |
---|
27 | Earnings | 1/1/2023 | 922.16 |
---|
28 | Federal Taxable Wages | 1/1/2023 | 777.94 |
---|
29 | Medicare (HI) Taxable Wages | 1/1/2023 | 870.16 |
---|
30 | Pre-Tax Deductions | 1/1/2023 | 144.22 |
---|
31 | Social Security Taxable Wages | 1/1/2023 | 870.16 |
---|
32 | State Taxable Wages | 1/1/2023 | 777.94 |
---|
33 | Total Taxes | 1/1/2023 | 146.53 |
---|
34 | | | |
---|
35 | | | |
---|
36 | | | |
---|
37 | | | |
---|
38 | | | |
---|
|
---|