How to transpose and link data from one sheet to a separate excel sheet?

[saadks]

Hi,

I have two identical tables in Sheet 1 as given below:

[TABLE="class: cms_table_grid, width: 360"]
<tbody>[TR]
[TD]Sr. No.[/TD]
[TD]Header 0[/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[/TR]
[TR]
[TD]Response 1[/TD]
[TD]Parameter 1[/TD]
[TD]Data A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Response 1[/TD]
[TD]Parameter 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Response 1[/TD]
[TD]Parameter 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD]Sr. No.[/TD]
[TD]Header 0[/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[/TR]
[TR]
[TD]Response 2[/TD]
[TD]Parameter 1[/TD]
[TD]Data B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Response 2[/TD]
[TD]Parameter 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Response 2[/TD]
[TD]Parameter 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: cms_table_grid, width: 360"]
<tbody>[TR]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
[TR]
[TD="colspan: 4"]Row with some other data[/TD]
[/TR]
</tbody>[/TABLE]


The two tables with Headers and parameters always have 4 rows of space between them. I have a huge set of tables in this way and I would like to transpose the data from these tables into a separate sheet while linking the cells.

So, for eg., in Sheet 2, I have the following table:

[TABLE="class: cms_table_grid, width: 946"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 3"]Header 1[/TD]
[TD="colspan: 3"]Header 2[/TD]
[TD="colspan: 3"]Header 3[/TD]
[/TR]
[TR]
[TD]Sr. No.[/TD]
[TD]Parameter 1[/TD]
[TD]Parameter 2[/TD]
[TD]Parameter 3[/TD]
[TD]Parameter 1[/TD]
[TD]Parameter 2[/TD]
[TD]Parameter 3[/TD]
[TD]Parameter 1[/TD]
[TD]Parameter 2[/TD]
[TD]Parameter 3[/TD]
[/TR]
[TR]
[TD]Response 1[/TD]
[TD]Data A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Response 2[/TD]
[TD]Data B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Is it possible to link the two sheets and ensure that all recurring tables, the values are picked up - Response 3, Response 4, etc. - by writing a macro/function. I need Data A and Data B and so on to show up in the right cells each time.

Not sure if my question is entirely clear. Let me know if I need to clarify on some point.

Thanks for the help!

 

[Macropod]

This is a duplicate of: https://www.mrexcel.com/forum/excel...nk-data-separate-excel-sheet.html#post4917832
Kindly read the forum rules (http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html), especially Rule 12, and don't post duplicates.

Thread closed.