Hi everyone
I am looking for a formula rather than VBA code or a macro to transfer data from a master list on sheet 1 to autopopulate in sheet 2 based on IDs and Headers.
In sheet 1, I would have data such as
[TABLE="class: grid, width: 512, align: left"]
<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]
[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Currency[/TD]
[TD]Value[/TD]
[TD]Pricing Year[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD]Core Data[/TD]
[TD]GBP[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]2012[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD] [/TD]
[TD]CAD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2013[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2014[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]2012[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
</tbody>[/TABLE]
I want to populate sheet 2 automatically based on the header and ID to create
[TABLE="class: grid, width: 256, align: left"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD="align: right"]50000[/TD]
[/TR]
</tbody>[/TABLE]
I guess that it is done using INDEX and MATCH, but what is the correct syntax in the formula?
Thank you
I am looking for a formula rather than VBA code or a macro to transfer data from a master list on sheet 1 to autopopulate in sheet 2 based on IDs and Headers.
In sheet 1, I would have data such as
[TABLE="class: grid, width: 512, align: left"]
<colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]
[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Description[/TD]
[TD]Currency[/TD]
[TD]Value[/TD]
[TD]Pricing Year[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD]Core Data[/TD]
[TD]GBP[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]2012[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD] [/TD]
[TD]CAD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2013[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]20000[/TD]
[TD="align: right"]2014[/TD]
[TD]Capital[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]2012[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD] [/TD]
[TD]USD[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]2015[/TD]
[TD]Revenue[/TD]
[/TR]
</tbody>[/TABLE]
I want to populate sheet 2 automatically based on the header and ID to create
[TABLE="class: grid, width: 256, align: left"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1.01[/TD]
[TD]Test 1[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1.02[/TD]
[TD]Test 2[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]1.03[/TD]
[TD]Test 3[/TD]
[TD="align: right"]20000[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]1.04[/TD]
[TD]Test 4[/TD]
[TD="align: right"]30000[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]1.05[/TD]
[TD]Test 5[/TD]
[TD="align: right"]40000[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]1.06[/TD]
[TD]Test 6[/TD]
[TD="align: right"]50000[/TD]
[/TR]
</tbody>[/TABLE]
I guess that it is done using INDEX and MATCH, but what is the correct syntax in the formula?
Thank you
Last edited: