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
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.
15 September 2006, 2:40 pmjkpieterse:
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.
16 September 2006, 3:10 amXL-Dennis:
Jan Karel,
It works with Excel 2007 B2TR as well
Kind regards,
16 September 2006, 10:30 amDennis
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
18 September 2006, 5:35 amSam
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. […]
20 September 2006, 4:15 am