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:
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!