Drag horizontal with spaces

[JimmyBambo]

Hi friends,
I have one issue which I cant resolve.
So I have sheet1 with vertical list:
Name
Name1
Name2
Name3
Name4 etc...

And I have to link it on sheet2, but on every fourth cell and horizontal.
So it should be in link like:
Name (empty cell) (empty cell) (empty cell) Name1 (empty cell) (empty cell) (empty cell) Name2 (empty cell)(empty cell)(empty cell) etc...
I could do that by linking cell by cell, but I have 1000 "Name" cells in sheet1 which should be transfered on sheet2, so the easiest way will be with dragging.

Thank you in advance

 

[Special-K99]

Assuming data starts in Sheet1!A1

in Sheet2!A1
=IF(MOD(COLUMN()-1,4)=0,INDEX(Sheet1!$A$1:$A$1000,QUOTIENT(COLUMN()-1,4)+1,1),"")
and copy across the columns

 

[JimmyBambo]

Thank you for your fast reply. Formula works, but unfortunately, not in my case.
https://ibb.co/dQBrgPn
Here is the picture of problem.
Name 1 should be in "AE" column, instead of that, I got NAME 8 in "AG" column.
Since I am not very familiar with these commands, I made somewhere mistake, but I cant recognize where.

 

[JimmyBambo]

Thank you.
I changed some parameters in formula, and now it works.
Unfortunately, I lost "center across selection" alignment.
Is is any chance to make "center across selection" in this case?

 

[Special-K99]

I've never used "Center across alignment" but thats sounds like a formatting thing.
Just format the cells.
This is not possible with a formula (I think).

 

[Rick Rothstein]

Here is a macro that will put a direct formula reference in AE1, AI1, AM1, AQ1, etc. on Sheet2 to the names in cells A1, A2, A3, etc. on Sheet1 and then automatically use Center Across Selection on the formula cell along with its next 3 adjacent cells.

Sub ReferenceNames()
  Dim R As Long, LastRow As Long
  LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
  Sheets("Sheet2").Range("AE1", Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft)).Clear
  For R = 1 To LastRow
    With Sheets("Sheet2").Range("AA1").Offset(, 4 * R)
      .Formula = "=Sheet1!A" & R
      .Resize(, 4).HorizontalAlignment = xlHAlignCenterAcrossSelection
    End With
  Next
End Sub

Note: If you change the list of names on Sheet1, simply run the macro again and it will clear out the old formula references and reestablish them (along with the formatting) for the new name list.

 

[JimmyBambo]

Thank you. This VBA works perfectly.
Can you please change only to start taking amounts from sheet1 A2 (since A1 is reserved for description of column)?

 

[Rick Rothstein]

Thank you. This VBA works perfectly.
Can you please change only to start taking amounts from sheet1 A2 (since A1 is reserved for description of column)?

This should do it...

Sub ReferenceNames()
  Dim R As Long, LastRow As Long
  LastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
  Sheets("Sheet2").Range("AE1", Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft)).Clear
  For R = 2 To LastRow
    With Sheets("Sheet2").Range("W1").Offset(, 4 * R)
      .Formula = "=Sheet1!A" & R
      .Resize(, 4).HorizontalAlignment = xlHAlignCenterAcrossSelection
    End With
  Next
End Sub