How to drag columns to the right and change row reference by one for every 6 columns dragged

[ddgale]

Hi Mr. Excel,

How can I drag columns to the right and change row reference by 1 every six columns, for example:

Sheet2 A column will reference Sheet1 $A$2
Sheet2 G column will reference Sheet1 $A$3
Sheet2 M column will reference Sheet1 $A$4

Ideally I would like to drag a range from Sheet2, let's say A:F and move it indefinitely and have the above happen (Row reference change by 1 for every 6 columns dragged to the right).

Is this possible? I have tried offset but I am sure I don't understand the function fully to use it at it's best capacity.

Any help is appreciated!

 

[Domenic]

Let's say that Sheet1!A2:F10 contains your data. Try...

Sheet2!A2, copied across:

=INDEX(Sheet1!$A$2:$F$10,INT((COLUMNS($A2:A2)-1)/6)+1,MOD(COLUMNS($A2:A2)-1,6)+1)

Change the range, A2:F10, accordingly.

Hope this helps!

 

[ddgale]

It worfed for the first set of columns but then it stopped working, wondering if it has limitations...

 

[Domenic]

Did you adjust the formula according to your range of data?

Which rows contain your data?

Can you post the exact formula that you tried?

 

[ddgale]

I did make the necessary adjustments, I believe. See below:

=ArrayFormula((IFERROR(INDEX('Data Dump'!$A$2:$D$5000,SMALL(IF('Data Dump'!$A$2:$D$5000=INDEX('MTBF MTTR'!$A$2:$F$30,INT((COLUMNS($A2:A2)-1)/6)+1,MOD(COLUMNS($A2:A2)-1,6)+1),ROW('Data Dump'!$A$2:$D$5000)-1,""),ROWS(A$3:3)),1),"")))

I underlined the area of interest.

 

[rajm11]

hi I am looking for something like, if I try I will post the result by tomorrow

 

[ddgale]

This is kinda how it looks, the A3 cell (bolded and resized) is the number

1000632383, this column is actually a dynamic table and the B:F columns are just VLOOKUPS of the A column. Everything repeats itself for the next set of columns and so forth, I would like to drag to the right the A:F data for several hundred columns.​

Orders	Breakdown duration	Malfunction start	Start of Malfunctn (Time)	Malfunction end	Malfunctn End (Time)	Orders	Breakdown duration	Malfunction start	Start of Malfunctn (Time)	Malfunction end	Malfunctn End (Time)

<colgroup><col style="width: 182px"><col width="120"><col width="103"><col width="180"><col width="180"><col width="131"><col width="120"><col width="120"><col width="120"><col width="151"><col width="120"><col width="130"></colgroup><tbody>
[TD="colspan: 6, align: center"]2[/TD]
[TD="colspan: 6, align: center"]3[/TD]

[TD="align: right"] 1000632383 [/TD]
[TD="align: right"]19.99[/TD]
[TD="align: right"]8/10/2011[/TD]
[TD="align: right"]4:01:06 PM[/TD]
[TD="align: right"]8/11/2011[/TD]
[TD="align: right"]12:00:20 PM[/TD]
[TD="align: right"]1000810686[/TD]
[TD="align: right"]61.13[/TD]
[TD="align: right"]6/30/2012[/TD]
[TD="align: right"]7:00:00 AM[/TD]
[TD="align: right"]7/2/2012[/TD]
[TD="align: right"]8:07:35 PM[/TD]

[TD="align: right"]1000733978[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2/10/2012[/TD]
[TD="align: right"]5:00:00 PM[/TD]
[TD="align: right"]2/11/2012[/TD]
[TD="align: right"]1:00:00 AM[/TD]
[TD="align: right"]1001040634[/TD]
[TD="align: right"]8.13[/TD]
[TD="align: right"]8/7/2013[/TD]
[TD="align: right"]7:00:00 AM[/TD]
[TD="align: right"]8/7/2013[/TD]
[TD="align: right"]3:07:32 PM[/TD]

[TD="align: right"]1000736089[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2/16/2012[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[TD="align: right"]2/17/2012[/TD]
[TD="align: right"]1:00:00 AM[/TD]
[TD="align: right"]1001040824[/TD]
[TD="align: right"]56[/TD]
[TD="align: right"]8/6/2013[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]8/8/2013[/TD]
[TD="align: right"]8:00:00 PM[/TD]

[TD="align: right"]1000750366[/TD]
[TD="align: right"]9.33[/TD]
[TD="align: right"]3/8/2012[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[TD="align: right"]3/9/2012[/TD]
[TD="align: right"]12:20:00 AM[/TD]
[TD="align: right"]1001043906[/TD]
[TD="align: right"]32.69[/TD]
[TD="align: right"]8/15/2013[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD="align: right"]8/16/2013[/TD]
[TD="align: right"]7:41:09 PM[/TD]

[TD="align: right"]1000791205[/TD]
[TD="align: right"]183.88[/TD]
[TD="align: right"]5/15/2012[/TD]
[TD="align: right"]3:00:00 AM[/TD]
[TD="align: right"]5/22/2012[/TD]
[TD="align: right"]6:52:43 PM[/TD]
[TD="align: right"]1001056780[/TD]
[TD="align: right"]4228.13[/TD]
[TD="align: right"]9/2/2013[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD="align: right"]2/25/2014[/TD]
[TD="align: right"]3:07:36 PM[/TD]

[TD="align: right"]1000899502[/TD]
[TD="align: right"]27.01[/TD]
[TD="align: right"]12/3/2012[/TD]
[TD="align: right"]3:10:00 AM[/TD]
[TD="align: right"]12/4/2012[/TD]
[TD="align: right"]6:10:45 AM[/TD]
[TD="align: right"]1001059605[/TD]
[TD="align: right"]3.67[/TD]
[TD="align: right"]9/5/2013[/TD]
[TD="align: right"]2:00:00 PM[/TD]
[TD="align: right"]9/5/2013[/TD]
[TD="align: right"]5:40:30 PM[/TD]

[TD="align: right"]1000917427[/TD]
[TD="align: right"]120.99[/TD]
[TD="align: right"]1/12/2013[/TD]
[TD="align: right"]5:51:00 AM[/TD]
[TD="align: right"]1/17/2013[/TD]
[TD="align: right"]6:50:08 AM[/TD]
[TD="align: right"]1001059606[/TD]
[TD="align: right"]3.71[/TD]
[TD="align: right"]9/5/2013[/TD]
[TD="align: right"]2:00:00 PM[/TD]
[TD="align: right"]9/5/2013[/TD]
[TD="align: right"]5:42:23 PM[/TD]

[TD="align: right"]1000920326[/TD]
[TD="align: right"]155.74[/TD]
[TD="align: right"]1/22/2013[/TD]
[TD="align: right"]3:15:26 AM[/TD]
[TD="align: right"]1/28/2013[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[TD="align: right"]1001556118[/TD]
[TD="align: right"]3.89[/TD]
[TD="align: right"]9/28/2015[/TD]
[TD="align: right"]11:13:35 AM[/TD]
[TD="align: right"]9/28/2015[/TD]
[TD="align: right"]3:07:16 PM[/TD]

[TD="align: right"]1001078833[/TD]
[TD="align: right"]1.42[/TD]
[TD="align: right"]10/3/2013[/TD]
[TD="align: right"]3:00:00 PM[/TD]
[TD="align: right"]10/3/2013[/TD]
[TD="align: right"]4:25:09 PM[/TD]

[TD="align: right"]1001087112[/TD]
[TD="align: right"]18.58[/TD]
[TD="align: right"]10/18/2013[/TD]
[TD="align: right"]11:00:00 AM[/TD]
[TD="align: right"]10/19/2013[/TD]
[TD="align: right"]5:35:00 AM[/TD]

[TD="align: right"]1001091226[/TD]
[TD="align: right"]103.02[/TD]
[TD="align: right"]10/29/2013[/TD]
[TD="align: right"]12:00:00 PM[/TD]
[TD="align: right"]11/2/2013[/TD]
[TD="align: right"]7:00:58 PM[/TD]

[TD="align: right"]1001138409[/TD]
[TD="align: right"]42[/TD]
[TD="align: right"]1/7/2014[/TD]
[TD="align: right"]7:00:00 AM[/TD]
[TD="align: right"]1/9/2014[/TD]
[TD="align: right"]1:00:00 AM[/TD]

[TD="align: right"]1001221318[/TD]
[TD="align: right"]48.5[/TD]
[TD="align: right"]5/8/2014[/TD]
[TD="align: right"]4:00:00 PM[/TD]
[TD="align: right"]5/10/2014[/TD]
[TD="align: right"]4:30:00 PM[/TD]

</tbody>

 

[Domenic]

Did you change the location of the first cell in which you're entering the formula? If so, you'll need to adjust this part of the formula too...

COLUMNS($A2:A2)

So, for example, if the first cell in which you're entering the formula is B2 instead of A2, you'll need to change it to...

COLUMNS($B2:B2)

 

[ddgale]

I did...

I could send you the file through PM if that is easier.

Let me know.

 

[Domenic]

Can you please clarify which cells contain the source data, and into which cells you want to place the results?

I'm currently not at my computer, but I'll have a look at it when I get a chance.