Recurring Post Formula

[wardy0601]

Hi All,

Hope everyone has had a nice Christmas and all the best for the new year!

I have sheet I have built where the date and row number appears for every day for a 30 year period.

There is an input page separate to the date page for all inputs to be made with results for earning and expenditure to go into the relevant column on the date page

My question is, does anyone know of a formula that will allow me to start at say 15 December and automatically input a recurring wage every 7/14/30 days on the date page (depending on what frequency of payment is selected)? It will also need to stop once it gets to the date stop date on the input page

Thanks in advance.

 

[wardy0601]

[TABLE="width: 755"]
<tbody>[TR]
[TD]INCOME[/TD]
[TD][/TD]
[TD]Date Start[/TD]
[TD]Date Stop[/TD]
[TD][/TD]
[TD]Date Start[/TD]
[TD]Date Stop[/TD]
[/TR]
[TR]
[TD]Gross Annual Income[/TD]
[TD] $ 130,000.00[/TD]
[TD="align: right"]30/12/2017[/TD]
[TD="align: right"]30/12/2047[/TD]
[TD] $ 40,000.00[/TD]
[TD="align: right"]3/01/2018[/TD]
[TD="align: right"]30/12/2047[/TD]
[/TR]
[TR]
[TD]Payment Cycle[/TD]
[TD]Fortnightly[/TD]
[TD][/TD]
[TD][/TD]
[TD]Weekly[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gross Income Per Cycle[/TD]
[TD] $ 5,000.00[/TD]
[TD="align: right"]30/12/2017[/TD]
[TD="align: right"]30/12/2047[/TD]
[TD] $ 769.23[/TD]
[TD="align: right"]3/01/2018[/TD]
[TD="align: right"]30/12/2047[/TD]
[/TR]
[TR]
[TD]Net Income Per Cycle[/TD]
[TD] $ 3,612.31[/TD]
[TD="align: right"]30/12/2017[/TD]
[TD="align: right"]30/12/2047[/TD]
[TD] $ 681.34[/TD]
[TD="align: right"]3/01/2018[/TD]
[TD="align: right"]30/12/2047[/TD]
[/TR]
</tbody>[/TABLE]

Here is what the set-up looks like on the input page. I would want the net income per fortnight in example 1 to be posted in the date page under the name of the person for the relevant date

Date page looks like this:

[TABLE="width: 372"]
<tbody>[TR]
[TD]Row Number[/TD]
[TD]Date[/TD]
[TD]Mortgage Repayments[/TD]
[TD]Pat Muzyk Income[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]15 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]17 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]18 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]19 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]20 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]21 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]22 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]23 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]24 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]25 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]26 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]27 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]28 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]29 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]30 Dec 2017[/TD]
[TD] $ -[/TD]
[TD] $ 3,612.31[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.
If I understand your need correctly .... this macro method will ask for "Paste the value every X rows" and then ask for the amount to be pasted.
It is hard coded to paste the chosen value in Column C (as per your example). That can be changed if required.

Option Explicit


Sub PasteEvery()
Dim j As String
Dim x As Integer
Dim i As Integer


x = InputBox("Enter every ? row to paste : " _
                 & Chr(13) & Chr(13) & "e.g: 7 for every 7th row.", "Paste Every ? Row ")


                 
j = InputBox("Enter dollar amount to paste : " _
                 & Chr(13) & Chr(13) & "e.g: $10.75 ", "Enter Dollar Amount ")






For i = 2 To 10951 Step x
     Cells(i, 3).Value = j   ' fill C2 through C10951 with the value of i
Next i
End Sub

 

[wardy0601]

Thanks for the reply Logit!

I will need a formula as there are many budget items to go into each row and it will be used by our staff. Having to manually adjust all of these for each item will def result in errors and the macro being ruined.

Thanks just the same.

 

[wardy0601]

All good thanks everyone, I have figured it out:

IF(AND($A14>=Inputs!$N$6,$B14<Inputs!$G$5,(MOD(ROWS($A$2:$A14)-Inputs!$N$6,Inputs!$E$3))+1=1),Inputs!$E$8,0)


<inputs!$g$5,(mod(rows($a$2:$a14)-inputs!$n$6,inputs!$e$3))+1=1),inputs!$e$8,0)< html=""></inputs!$g$5,(mod(rows($a$2:$a14)-inputs!$n$6,inputs!$e$3))+1=1),inputs!$e$8,0)<>