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!

 

[ddgale]

A3:20 range is a dynamic table at contains the source data. The remaining 5 columns that follow are vlookups which are dependent on column A data. When I drag the range A:F towards the right, it works until column EU.

 

[Domenic]

You didn't say where you want to return the results. In any case, I'm a little confused. Let's say that you have the following data...

Sheet1!A3:F20

[TABLE="width: 384"]
<tbody>[TR]
[TD="class: xl63, width: 64"]x[/TD]
[TD="class: xl63, width: 64"]x1[/TD]
[TD="class: xl63, width: 64"]x2[/TD]
[TD="class: xl63, width: 64"]x3[/TD]
[TD="class: xl63, width: 64"]x4[/TD]
[TD="class: xl63, width: 64"]x5[/TD]
[/TR]
[TR]
[TD="class: xl63"]y[/TD]
[TD="class: xl63"]y1[/TD]
[TD="class: xl63"]y2[/TD]
[TD="class: xl63"]y3[/TD]
[TD="class: xl63"]y4[/TD]
[TD="class: xl63"]y5[/TD]
[/TR]
[TR]
[TD="class: xl63"]z[/TD]
[TD="class: xl63"]z1[/TD]
[TD="class: xl63"]z2[/TD]
[TD="class: xl63"]z3[/TD]
[TD="class: xl63"]z4[/TD]
[TD="class: xl63"]z5[/TD]
[/TR]
[TR]
[TD="class: xl63"]'etc[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]'[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]'[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="class: xl63"]'[/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
</tbody>[/TABLE]

And, let's say that you want to return the results to the second row of Sheet2, starting at A2. Based on my sample data, the formula will return the following results...

Sheet2!A2:DD2

[TABLE="width: 1344"]
<tbody>[TR]
[TD="class: xl65, width: 64"]x[/TD]
[TD="class: xl65, width: 64"]x1[/TD]
[TD="class: xl65, width: 64"]x2[/TD]
[TD="class: xl65, width: 64"]x3[/TD]
[TD="class: xl65, width: 64"]x4[/TD]
[TD="class: xl65, width: 64"]x5[/TD]
[TD="class: xl65, width: 64"]y[/TD]
[TD="class: xl65, width: 64"]y1[/TD]
[TD="class: xl65, width: 64"]y2[/TD]
[TD="class: xl65, width: 64"]y3[/TD]
[TD="class: xl65, width: 64"]y4[/TD]
[TD="class: xl65, width: 64"]y5[/TD]
[TD="class: xl65, width: 64"]z[/TD]
[TD="class: xl65, width: 64"]z1[/TD]
[TD="class: xl65, width: 64"]z2[/TD]
[TD="class: xl65, width: 64"]z3[/TD]
[TD="class: xl65, width: 64"]z4[/TD]
[TD="class: xl65, width: 64"]z5[/TD]
[TD="class: xl65, width: 64"]'etc[/TD]
[TD="class: xl65, width: 64"]'[/TD]
[TD="class: xl65, width: 64"]'[/TD]
[/TR]
</tbody>[/TABLE]

Here's the formula...

Sheet2!A2, copied across to DD2:

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

If you don't want to limit the range to Rows 3 through 20, you could use the following formula instead...

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

Hope this helps!

 

[ddgale]

OK, I think we are talking about different things. I will explain to the best of my ability while keeping things relatively simple. Taking your table as a starting point, see below:

Sheet1!A3:F20

[TABLE="class: cms_table, width: 384"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 64"]x[/TD]
[TD="class: cms_table_xl63, width: 64"]x1[/TD]
[TD="class: cms_table_xl63, width: 64"]x2[/TD]
[TD="class: cms_table_xl63, width: 64"]x3[/TD]
[TD="class: cms_table_xl63, width: 64"]x4[/TD]
[TD="class: cms_table_xl63, width: 64"]x5[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]y[/TD]
[TD="class: cms_table_xl63"]y1[/TD]
[TD="class: cms_table_xl63"]y2[/TD]
[TD="class: cms_table_xl63"]y3[/TD]
[TD="class: cms_table_xl63"]y4[/TD]
[TD="class: cms_table_xl63"]y5[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]z[/TD]
[TD="class: cms_table_xl63"]z1[/TD]
[TD="class: cms_table_xl63"]z2[/TD]
[TD="class: cms_table_xl63"]z3[/TD]
[TD="class: cms_table_xl63"]z4[/TD]
[TD="class: cms_table_xl63"]z5[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]'etc[/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]'[/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]'[/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl63"]'[/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[TD="class: cms_table_xl63"][/TD]
[/TR]
</tbody>[/TABLE]


Based on this table, I don't really care about X1, X2... 5, Y1, Y2... 5, or Z1, Z2... 5. These cells are really dependent on x, y, and z so ultimately I only care about the A column.

Let's talk about the A column... The A column is a dynamic table, it grabs data from a different sheet that contains hundreds of data points, but mainly bases the dynamic table generation by searching for a single reference point. Let's say this reference point is an unique number such as 12345, column A will search for all of those values (within the sheet with hundreds of data points) that match and sends back a result. I hope this is clear and not confusing... Now, the identifier (12345) or reference resides in a table on a different sheet. I have hundreds of unique identifiers and Column A represents a single one.

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"]A2[/TD]
[TD="colspan: 6, align: center"]A3[/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>

Table above represents my workbook. The "Orders" column is the A column which is dynamically searching so it changes the amount of data depending on the reference value. Column B:H are vlooking column A. The reference value is one of a list of hundreds (Sheet2!C3, C5... C250). Therefore I want to mimic what is happening on A:F (or A2 merged cell area) without having to change every column reference cell to C4, C5... C250. For exmaple, the A3 merged area (or Column H) is using Sheet2!C4, whereas Column A is using Sheet2!C3. Hence wanting to change row reference by 1 for every 6 columns dragged to the right.

Does that make sense?? Thanks so much for your help, you have been very helpful!