Autofill non-consecutive cells from a consecuative list

G

g4reg

New member
Joined
Aug 14, 2016
Messages
5
Reaction score
0
Points
0
Hi,

What I am wanting to do, is to auto fill the consecutive values from a particular row on one sheet in my workbook to another sheet but the cells in the row where the values are going are non consecutive. To help explain I will give an example. I want to copy values from cell B3, C3, D3 etc up to say Z3 from sheet1. They will fill cells E5, K5, Q5 etc on sheet2. I have tried using an INDEX formula that I have seen used else where but I'm not really sure how to use it plus the scenario was slightly different to mine and was dealing with columns instead of rows.

Thanking you in advance

Greg
 
The values in question are cash amounts, the spreadsheet is for personal finances. I am using the values from sheet 1 as part of a simple adding calculation in sheet 2. These cells are all in currency format. Thanks for the replie I hope this is the information you require.
Greg
 
You want the exact value as follows
B3 = E5
C3 = K5
???
 
Yes, the values in sheet1 are to be directly copied on to sheet2. So E5 in sheet2 will equal the value of B3 on sheet1 and so on. If i just use the formula =sheet1!B3 in cell E5 on sheet2, the correct value is copied over. The problem arises when i copy that formula into K5 on sheet2. Instead of receiving the value from cell C3 on sheet1, which is what I want, the formula pasted is =sheet1!H3. Even though i have moved 6 cells across on sheet2 for the "pasting" I am only moving 1 cell across for the "coping". The reason for the formula instead of just manually coping each cell over is the amount in question, a lot.
Greg
 
a macro to put in the formulas?
maybe this....

Code: 
Sub InsertFormulas()
    Dim i As Integer
    Dim ReadCol As Long, WriteCol As Long
'starting points
ReadCol = -2    '<- col C as ref'd from col E    
WriteCol = 5
For i = 1 To 24     '<- 24 cells to be copied
    Sheets("Sheet2").Cells(5, WriteCol).FormulaR1C1 = "=Sheet1!R[-2]C[" & ReadCol & "]"
    WriteCol = WriteCol + 6
    ReadCol = -2 - (i * 5)
Next i
End Sub
 
Last edited:
If you prefer a formula then try to E5 on Sheet2. Copy the right
Code: 
=IFERROR(INDIRECT("Sheet1!"&CHAR(64+IF(MOD(COLUMN()+1;6)=0;COUNTIF($E4:E4;MOD(COLUMN()+1;6));"")+2)&3);"")
In row 4 is the helper formula
 

Attachments

  • g4reg-navic1.xlsx
    10 KB · Views: 19
Last edited:
Cheers for the replies I will update on my progress as soon I get chance to try your solutions
Greg
 
After a busy week I have looked at the solutions presented and can report back success with macro. I tried the formula first but unfortunately couldn’t get it to work. Thank you the formula I’m sure the fault was mine with how I used it. With respect to the macro not only did it perform the task required, after a crash course in macro’s I was able to manipulate the code so I could perform the same task on other rows and columns.
Many thanks for the support in resolve my problem
Greg

 
Thanks Greg,

We appreciate you reporting back and are always glad to help.
 
You must log in or register to reply here.
Back
Top