Recurring Post Formula

wardy0601

Board Regular
Joined
Sep 24, 2011
Messages
117
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

Board Regular
Joined
Sep 24, 2011
Messages
117
INCOMEDate StartDate StopDate StartDate Stop
Gross Annual Income $ 130,000.0030/12/201730/12/2047 $ 40,000.003/01/201830/12/2047
Payment CycleFortnightlyWeekly
Gross Income Per Cycle $ 5,000.0030/12/201730/12/2047 $ 769.233/01/201830/12/2047
Net Income Per Cycle $ 3,612.3130/12/201730/12/2047 $ 681.343/01/201830/12/2047

<tbody>
</tbody>

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:

Row NumberDateMortgage RepaymentsPat Muzyk Income
115 Dec 2017 $ - $ -
216 Dec 2017 $ - $ -
317 Dec 2017 $ - $ -
418 Dec 2017 $ - $ -
519 Dec 2017 $ - $ -
620 Dec 2017 $ - $ -
721 Dec 2017 $ - $ -
822 Dec 2017 $ - $ -
923 Dec 2017 $ - $ -
1024 Dec 2017 $ - $ -
1125 Dec 2017 $ - $ -
1226 Dec 2017 $ - $ -
1327 Dec 2017 $ - $ -
1428 Dec 2017 $ - $ -
1529 Dec 2017 $ - $ -
1630 Dec 2017 $ - $ 3,612.31

<tbody>
</tbody>
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,924
.
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.

Code:
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

Board Regular
Joined
Sep 24, 2011
Messages
117
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

Board Regular
Joined
Sep 24, 2011
Messages
117
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)<>
 
Last edited:

Forum statistics

Threads
1,082,153
Messages
5,363,459
Members
400,737
Latest member
urhen22

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top