1

I believe this question has been asked a couple of times and resources exist on the web on how to transpose column data. The issue is most of the available answers don't deal with actual Excel tables (i.e. most of the transposing available is carried out on column data not an Excel table column data). They also don't deal with automation.

The reason I have specified the difference is because an Excel table allows you add extra rows. Therefore if I transposed this Excel Table Column Data to column headings for a different table and I added a new row in my table does my new table row of column headings update itself to have an extra column.

Excel Table Column Data

The copy and paste transpose method works by copying the cell range and pasting using Excel Table Column Data that will be cell B3 to B8. This the Transposed Data:

Transposed Data

If I therefore added a new row, i.e column data, to my table, how do I get an updated table with an extra column? For instance, if I add a new entry in B9 (to Excel Table Column Data) as "Thine", how can it be automatically updated as a new entry in O3 (from Transposed Data)?

Improve this question

1 Answer 1

Reset to default
0

Use the TRANSPOSE() formula to transpose on the fly. Assuming your Table is called Table2, select a range where you want the transposed data to be and type =TRANSPOSE(Table2[Heading]) and press Ctrl+Shift+Enter as opposed to just Enter when you type the formula. This creates an array formula, i.e. a formula that returns an array of values rather than a single value.

I would select a range of N cells where N is what I think is a theoretical maximum number of rows I expect to have on my table in the near future.

enter image description here

enter image description here

Improve this answer
3
  • Thanks for your answer. It solved my problem quite well. The only existing issue is the N/A error on the extra columns in the theoretical maximum. I could see above you were trying to use the IFERROR command but in the row beneath (i.e. Row 3). I tried to do it in one go for row 2 by writing '=IFERROR(TRANSPOSE(Categories), " ")' but it still doesn't work. Any suggestions?
    – MrMarho
    Oct 14, 2016 at 10:53
  • For some reason IFERROR doesn't work with TRANSPOSE -- or with any other array formula for thats matter, which is rather unfortunate
    – airstrike
    Oct 14, 2016 at 14:22
  • Although writing an alternative IFERROR function in VBA that works as you had hoped would be trivial. I'm sure someone has already done it, otherwise let me know and I'll write it for you.
    – airstrike
    Oct 14, 2016 at 14:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .