Transpose cells on another tab and link back to original cells

Question

I have a table I need to have in both the original and transposed views. However, I need to be able to update both sheets from ONE place.

How can I duplicate transposed cells to a second sheet with their values linked to the first sheet?

BEFORE:

Sheet1 has Products in column A (original)

AFTER:

Sheet1 has Poducts in column A (original)
Sheet2 has Products in row A (transposed)
Editing cell values in Sheet1 also updates Sheet2

score 0 · Answer 1 · answered Aug 18 '21 at 15:39

Andrew I do not know how big is your data and in what frequency its updated; a few solutions coming to my mind. 1- You can link cells from sheet1 to sheet2 for once and then you are good to go as you typed in sheet1, values in sheet2 will be updated instantly. 2- You can manually copy your data and paste special it as transposed. 3- You can use the macro below to copy selected range and paste special it as transposed. You will need to arrange the range to be pasted in the code according to your needs. Results of macro will be like below screenshot.

Sub Macro3()
    Selection.Copy
    Range("a14").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

score 0 · Answer 2 · answered Aug 18 '21 at 16:31

Personally I prefer to always go for a solution with Excel built in function and no scripts. In your case that should be possible with a few limitations. Here's what you can do:

Complete your original table
On a new tab create a formula (assuming the source starts at cell A1 and the transposed area in the new tab also starts in A1. Otherwise adjust the references accordingly):

=OFFSET(Sheet1!$A$1, COLUMN(A1) - 1, ROW(A1) - 1)
Copy the formula to cover all the source's cells.
Optionally copy the formating with right click -> paste special -> Paste formats and transpose.

The limitations are:

if you add or remove lines in your source area, you'll have to copy and paste the formula in your transposed area anew
whenever you change the format in your source, you'll have to manually copy it over your transposed area as well
if you have empty cells in your source, the transposed area will show these as 0

score 0 · Answer 3 · answered Aug 19 '21 at 07:41

As mentionned in a comment to your question, use the build-in tool PowerQuery / Get & Transform.
The transpose step is described on the docs.microsoft.com Website.
Before transposing, you may have to "demote" the column header of the imported table.

To update the transposed table, you have to refresh it manually (DATA > refresh all).

score 0 · Answer 4 · answered Aug 19 '21 at 08:02

Select the Range and go to Data- From Sheet- open Power Query Editor- select Color, Price, Note columns- go to Transform tab and select Unpivot Columns- select Name column- under Transform tab- select Pivot Column- Values Column: Value and Advanced options: Don't Aggregate- click OK and Close & Load to...:

Transpose cells on another tab and link back to original cells

4 Answers4