Search code examples
excelformulas

Problems with transposition of cell values in Excel

Is a question about excel. I should repeat horizontally (on the second sheet of the same file), the cell values that are arranged vertically. Also, when I'm going to change the values of the "source" cells, I would like the values to automatically update on the second sheet. Since the cells to be transposed are so many, I would like to ensure that the transposition starts from the first column (from the first sheet), then the second column and so on.. because I noticed that instead of continuing with the second column, the transposition goes to the second line.

Solution

  • If you have values in Sheet1 in Column A vertically as below,

    enter image description here

    Enter the formula in your other sheet in cell A1, and drag it to the right and then down,

    =OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)

    enter image description here