Possible with VBA? Payroll allocation changes

M

mgerner

New Member
Joined
Jun 16, 2018
Messages
5
I'm not sure if this is even possible using VBA and it's hard to explain, but I'll give it a try. I’ve been trying to figure this out for a few weeks with no luck. Basically, I have two worksheets with different payroll related data. Below is some sample data. On sheet1, I have 4 columns showing that the individuals payroll is split 50/50 between "Account1" and "Account2.” Then, on sheet2, I have 3 columns. Column A has the individuals name, column B has the account, and column C has the correct % allocation that the individuals payroll needs to be split by.

The % allocations on sheet1 (50/50 b/w “Account-1” and “Account-2” in the example below) are incorrect. Sheet2 has the correct allocation (60/20/20) for the individual. So, what I have been having to manually do, is figure out the total $ amount for the individual for SALY, USM, and USS, and then re-splitting out that amount by the correct % allocations.

Is this is even possible using VBA?

[TABLE="width: 276"]
<colgroup><col width="125" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4437;"> <col width="86" style="width: 64pt; mso-width-source: userset; mso-width-alt: 3043;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2673;"> <col width="83" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2958;"> <tbody>[TR]
[TD="class: xl69, width: 369, bgcolor: transparent, colspan: 4"]Starting data on Sheet1[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Name[/TD]
[TD="class: xl72, bgcolor: transparent"]Account[/TD]
[TD="class: xl72, bgcolor: transparent"]$ Amount[/TD]
[TD="class: xl72, bgcolor: transparent"]Allocation[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 50.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 50.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 5.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]50%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, colspan: 3"]Adjustments to be made on Sheet2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Name[/TD]
[TD="class: xl72, bgcolor: transparent"]Account[/TD]
[TD="class: xl72, bgcolor: transparent"]Allocation[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent, colspan: 4"]Desired outcome on Sheet3[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]Name[/TD]
[TD="class: xl72, bgcolor: transparent"]Account[/TD]
[TD="class: xl72, bgcolor: transparent"]$ Amount[/TD]
[TD="class: xl72, bgcolor: transparent"]Allocation[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 60.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 20.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - SALY[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl66, bgcolor: transparent"] 20.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 6.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USM[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-1[/TD]
[TD="class: xl66, bgcolor: transparent"] 6.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-2[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]John Smith - USS[/TD]
[TD="class: xl65, bgcolor: transparent"]Account-3[/TD]
[TD="class: xl66, bgcolor: transparent"] 2.00 [/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]20%[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
872
smozgur
smozgur
J
  • Question Question
Take Excel Worksheet, Divide into Worksheets/Workbooks by Employee/Card #, Email via Gmail to Employee for Input
Replies
9
Views
963
jennifer7i884
J
Rob_010101
  • Question Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K
O
  • Question Question
Fill in cells based off VBA User Form input
Replies
8
Views
1K
DanteAmor
DanteAmor
W
  • Question Question
Personnel Roster using Macro's
Replies
0
Views
340
womeclr
W

Forum statistics

Threads
1,226,730
Messages
6,192,698
Members
453,747
Latest member
tylerhyatt04

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top