• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

transpose table in a dashboard, columns to rows

J

jsto7380

New Member
Hello


I'm trying to create a sort of dashboard, and I'm looking to transpose certain columns to a different set of rows while maintaining links to the original table.


My question is masterfully answered in this post http://bit.ly/UzsUmr, however I cannot get it to work for the opposite ie columns to rows instead of rows to columns.


Anybody know how I could tweek the formula? Or a different solution?


The tables below show what Im trying to do: the Ys As and Cs are linked between the first and second table.

[pre]
Code: 
X	Y	Z	A	B	C
X1	Y1	Z1	A1	B1	C1
X2	Y2	Z2	A2	B2	C2
X3	Y3	Z3	A3	B3	C3
X4	Y4	Z4	A4	B4	C4
X5	Y5	Z5	A5	B5	C5
X6	Y6	Z6	A6	B6	C6
X7	Y7	Z7	A7	B7	C7
X8	Y8	Z8	A8	B8	C8
X9	Y9	Z9	A9	B9	C9
X10	Y10	Z10	A10	B10	C10
Y	Y1	Y2	Y3	Y4	Y5	Y6	Y7	Y8	Y9	Y10
A	A1	A2	A3	A4	A5	A6	A7	A8	A9	A10
C	C1	C2	C3	C4	C5	C6	C7	C8	C9	C10
[/pre]
Many many thanks in advance,
 
Hi jsto7380,


With your data assumed in Cell A1:F11, use this formula:


Code: 
=INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)*2))


Drag down/right.


Regards,
 
Thanks - that's really cool, and really really useful to know


However I don't need columns X Z B , so I need to be able to select the rows I am transposing.


Any ideas?
 
I did try - copied and pasted exactly as it is - and unfortunately I did get X Z B for some reason...


### sorry! the *2 dropped off somewhere before pasting!!! Thanks !!
 
I think it will work you need to adjust cell referencing, please see this:


http://dl.dropbox.com/u/60644346/jsto7380_TransposeTable.xlsx


Edit: Glad you found it!!! :p


Regards,
 
I'm sorry to trouble you again Faseeh, but due to the set up of my worksheet my table starts at B2 and not A1, and unfortunately the formula doesn't perform in the same way with this shift of cells.


Any ideas on how to tweak the formula so one might have control in describing the columns by position and length?


Edit: my table is B3:K46 ie 10 columns and I need every second one...
 
I have no idea what is happening but often when I am adding a link here in the forum I am unable to post. I have tried to add the link several times now with no luck with the file in skydive...


Edit: trying to add by editing http://sdrv.ms/UzBf9x


Edit 2: seems to have worked...
 
Hello again,


Is there anything I can do to help the 'electric load' - I'm not really sure what that means?


I just wanted to ask what the two formulas are doing, perhaps I could work out a solution...


Faseeh's formula: =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)*2))


Narayan's formula in a previous post: =OFFSET($Z$5,COLUMN(V24)-COLUMN($V$24),ROW(V24)-ROW($V$24))


Thanks again,


Edit: [previous and related post http://bit.ly/UzsUmr]
 
Jsto7380,


sorry for keep you waiting for almost 14 hours, plz check this one.


Code: 
=INDIRECT(ADDRESS(COLUMN(A3)+2,(ROW(A2)-1)*2))


Regards,
 
Hello,


It didn't work - I need the numbers not the helper column contents. The problem is that in the first instance it's necessary to skip three columns, and afterwards two.


I've been trying to work out how this formula works for hours now and still have no idea - any insights would be appreciated


Thanks,

Jana


Edit: the first column would be useful actually, and the number columns (ie every second column after column A)
 
You must log in or register to reply here.
Back
Top