I copied a large amount of data from Sheet 1 and transposed it into Sheet 2
so I could create a pivot table.

Now I would like to link the similarly valued cells together so that I only
have to enter a value once in sheet 1 so that it updates in sheet 2 to link
to the pivot table

Sheet1ActualSalesWeek1= Sheet2ActualSalesWeek1 etc

I can do this manually but it is very long and laborious. Because one sheet
is transposed I cannot click and drag and when I attempted a macro I
encountered a bug.

I would be grateful for any suggestions

So, I asume your row names from sheet 1 match your column names from sheet 2
and vice versa.

=INDEX(sheet1!$A$1:$G$4,MATCH(B$1,sheet1!$A$1:$A$4 ,0),MATCH($A2,sheet1!$A$1:$G$1,0))

This assumes your sheet1 tab is 4 ros by 7 columns. Change the $A$1:$G$4 to
encapture the entire table, $A$1:$A$4 to capture all rows of data and
$A$1:$G$1 to capture all columns of data.


"Art Caragh" wrote:

I copied a large amount of data from Sheet 1 and transposed it into Sheet 2
so I could create a pivot table.

Now I would like to link the similarly valued cells together so that I only
have to enter a value once in sheet 1 so that it updates in sheet 2 to link
to the pivot table

Sheet1ActualSalesWeek1= Sheet2ActualSalesWeek1 etc

I can do this manually but it is very long and laborious. Because one sheet
is transposed I cannot click and drag and when I attempted a macro I
encountered a bug.

I would be grateful for any suggestions