Automatic Entry 'Copy and paste' based of cell value

[FletchFletcher]

(Spreadsheet can be downloaded at the bottom of this post, with all notes included).

Hello peoples!

I've always enjoyed trying to crack formulas myself and used this forum many times to help me, thank you. This one's got me stumped and I would really appreciate a nod in the right direction.

I would like the 'VALUES' row (G7:R7) to be automatically populated from the 'DATA' row (G3:R3).

I would like to be able to change the numerical value under "NUMBER OF MONTHS TO REPEAT" (D7) so that the 'automatic copy and pasting' restarts 'copying and pasting' from the start of data into the next cell.

In the Examples above, I've provided colour coded 'VALUES' to simulate what this would look like based of the 'NUMBER OF MONTHS TO REPEAT' value on the left.

In Example 1, the value for 'NUMBER OF MONTHS TO REPEAT' is 6, you will see there are 6 red 'copy and pasted' values ranging from 100-600, it then starts again with a set of green values 100-600.

I've supplied these notes in the spreadsheet you can download from this dropbox link:
https://dl.dropboxusercontent.com/u/14447602/Pics/Fletch-automatic-copy-paste-based-of-value-V2.xlsx

Thank you for your time on this.

Fletch

 

[sergioMabres]

You may consider using a Function, I have taken the liberty of writing such function, here it is:

Function myfunct(start_month As Integer, num_of_month_to_repeat As Integer, this_month As Integer, values As Range) As Double
    Dim mtr As Integer
    If start_month > this_month Then
        myfunct = 0
    Else
        this_month = this_month - start_month + 1
        mtr = this_month Mod num_of_month_to_repeat
        If mtr = 0 Then
            mtr = num_of_month_to_repeat
        End If
        myfunct = values.Cells(1, mtr).Value
    End If
End Function

You can test the function in your modified workbook here
https://dl.dropboxusercontent.com/u/23094164/Fletch-automatic-copy-paste-based-of-value-V3.xlsm
Cheers
Sergio

 

[sergioMabres]

You can change the name of the function, myfunct is pretty silly, just tell me what you want the function to be named and I'll change it

 

[FletchFletcher]

Sergio, I can't thank you enough for this formula, there is no way I could have written that.
I've never written a function, but because I've spent so long on this problem I'm actually able to understand how you created this.

You just taught a man to catch a fish Have a great week my friend. Fletch

 

[sergioMabres]

It makes me very happy to help, it is so good to read your comments afterwards
Sergio