Update value in a column from another worksheet depending on the date in a range (1 Viewer)

raghuprabhu · Feb 24, 2018

Our new pay system at work went live in August 2017.
This is the new pay calendar (called PayPeriods)
Start Date End Date Pay period
10 Aug 17 23 Aug 17 PP0001
24 Aug 17 06 Sep 17 PP0002
07 Sep 17 20 Sep 17 PP0003
21 Sep 17 04 Oct 17 PP0004
05 Oct 17 18 Oct 17 PP0005
19 Oct 17 01 Nov 17 PP0006

This is the table for members (called MemberList)
Name Due Date Amount Processed in Pay Cal
Raghu 15 Jul 17 $248.00 PP0001
Vim 20 Jul 17 $354.00 PP0001
Abhi 10 Aug 17 $954.00 PP0001
Neelima 23 Aug 17 $134.00 PP0001
Raghu 14 Sep 17 $134.00 PP0003
Vim 21 Sep 17 $524.00 PP0004
Abhi 06 Oct 17 $332.00 PP0005
Neelima 20 Oct17 $158.00 PP0006
Raghu 06 Sep16 $456.00 PP0002
Vim 19 Sep 17 $159.00 PP0003
Abhi 03 Oct 17 $357.00 PP0004
Neelima 18 Oct 17 $852.00 PP0005

I want to update the Processed in Pay Cal column with the pay periods.

Anything with a due date on or 23 AUG 17 to be processed in PP0001 and the rest in the appropriate pay calendar. I need help with the code to write a macro.

Code:

Sub updateColunm4()
If duedate <= 23Aug17 then
	Update column 4 to PP0001
Else
	Update column 4 to the appropriate pay period
End if
End sub

Thank you

Regards
Raghu

Gasman · Feb 24, 2018

No need for macro.

Look at SUMPRODUCT

I do something simliar for values between two dates.

Look at columns K & L in sheets Gas or Electric in the attached workbook.

HTH

arnelgp · Feb 25, 2018

U can also use a simple If() and SumIfs().

raghuprabhu · Feb 25, 2018

thanks

arnelgp and Gasman.

Code:

Sub updateColunm4()
If duedate <= 23Aug17 then
	Update column 4 to PP0001
Else
	Update column 4 to the appropriate pay period
End if
End sub

arnelgp I can to the if bit, how do I do the else bit?

Thanks

Raghu

arnelgp · Feb 25, 2018

See the formula at Memberlist Sheet.

raghuprabhu · Feb 26, 2018

Thank Arnelgp

I will try this out.

Update value in a column from another worksheet depending on the date in a range (1 Viewer)

raghuprabhu

Registered User.

Gasman

Enthusiastic Amateur

Attachments

arnelgp

..forever waiting... waiting for jellybean!

raghuprabhu

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

raghuprabhu

Registered User.

Similar threads

Users who are viewing this thread