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.​

2	3
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)
1000632383	19.99	8/10/2011	4:01:06 PM	8/11/2011	12:00:20 PM	1000810686	61.13	6/30/2012	7:00:00 AM	7/2/2012	8:07:35 PM
1000733978	8	2/10/2012	5:00:00 PM	2/11/2012	1:00:00 AM	1001040634	8.13	8/7/2013	7:00:00 AM	8/7/2013	3:07:32 PM
1000736089	10	2/16/2012	3:00:00 PM	2/17/2012	1:00:00 AM	1001040824	56	8/6/2013	12:00:00 PM	8/8/2013	8:00:00 PM
1000750366	9.33	3/8/2012	3:00:00 PM	3/9/2012	12:20:00 AM	1001043906	32.69	8/15/2013	11:00:00 AM	8/16/2013	7:41:09 PM
1000791205	183.88	5/15/2012	3:00:00 AM	5/22/2012	6:52:43 PM	1001056780	4228.13	9/2/2013	11:00:00 AM	2/25/2014	3:07:36 PM
1000899502	27.01	12/3/2012	3:10:00 AM	12/4/2012	6:10:45 AM	1001059605	3.67	9/5/2013	2:00:00 PM	9/5/2013	5:40:30 PM
1000917427	120.99	1/12/2013	5:51:00 AM	1/17/2013	6:50:08 AM	1001059606	3.71	9/5/2013	2:00:00 PM	9/5/2013	5:42:23 PM
1000920326	155.74	1/22/2013	3:15:26 AM	1/28/2013	3:00:00 PM	1001556118	3.89	9/28/2015	11:13:35 AM	9/28/2015	3:07:16 PM
1001078833	1.42	10/3/2013	3:00:00 PM	10/3/2013	4:25:09 PM
1001087112	18.58	10/18/2013	11:00:00 AM	10/19/2013	5:35:00 AM
1001091226	103.02	10/29/2013	12:00:00 PM	11/2/2013	7:00:58 PM
1001138409	42	1/7/2014	7:00:00 AM	1/9/2014	1:00:00 AM
1001221318	48.5	5/8/2014	4:00:00 PM	5/10/2014	4:30:00 PM

<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>
</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.