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

ddgale

New Member
Joined
Feb 17, 2016
Messages
9
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!
 
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.
 
Upvote 0
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!
 
Upvote 0
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.

OrdersBreakdown durationMalfunction startStart of Malfunctn (Time)Malfunction endMalfunctn End (Time)OrdersBreakdown durationMalfunction startStart of Malfunctn (Time)Malfunction endMalfunctn 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!
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top