Transposing the Formulas in a Table of Cells

OK, time for some non Excel 2007 stuff…

Recently someone asked me if it was possible to transpose a table of cells, but in such a way that the formula of each cell is kept intact. I decided VBA was the way to go and produced a small but very useful little routine for that.

What the poster wanted is to go from:

To:

Read on here.

Regards,

Jan Karel Pieterse

JKP Application Development Services

Founding member of:
Professional Office Developers Association

5 Comments

  1. Mike Alexander:

    Great VBA Solution JKP!

    I often get this type of question, but usually from non-technical users.
    I typically give them this solution.

    - Find and Replace the equal (=) signs in the formulas with number (#) signs.
    - Transpose the range
    - Find and Replace the number (#) signs back into equal (=) signs.

    Since Excel does not recognize the formulas, it does not adjust the references.

  2. jkpieterse:

    I know. That works fine too. Thought I’d show how you could do it with VBA and use the variant method to pull/push properties from/to a range.

  3. XL-Dennis:

    Jan Karel,

    It works with Excel 2007 B2TR as well ;)

    Kind regards,
    Dennis

  4. sam:

    Jan Karel,

    I always wanted an “Copy Formula” option in the Dragdrop menu (when u right click on the border of a range of cell…drag and let go….)

    Inspired by the Custom Drag Drop file from Stephen and your post… I finally manged to do it !!

    Thanks to both of you…

    Regards
    Sam

  5. PDF to Excel conversion and other stuff » Excel Tip - Transposing a table:

    […] I didn’t initially intend to post tips, but the other day Jan Karel Pieterse from Daily Dose of Excel wrote an excellent post on how to transpose a table while keeping the formulas in the table intact. I was actually asked about this by one of our customers, so I thought I should share this with all of you. […]

Leave a comment