Drag horizontal with spaces

JimmyBambo

New Member
Joined
Dec 8, 2018
Messages
29
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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
 
Last edited:

JimmyBambo

New Member
Joined
Dec 8, 2018
Messages
29
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

New Member
Joined
Dec 8, 2018
Messages
29
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?
 
Last edited:

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,327
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).
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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.
Code:
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.
 
Last edited:

JimmyBambo

New Member
Joined
Dec 8, 2018
Messages
29
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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...
Code:
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
 

Forum statistics

Threads
1,081,969
Messages
5,362,484
Members
400,677
Latest member
champchamp

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