Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
I have worksheet #2 containing a table where all cells, including row and
column headings, contain links to values in worksheet #1 in the workbook. I want to "transpose" the table in #2 (including row and column headings) into a table in worksheet #3, so that the rows in #2 become columns on #3 and the columns in #2 become rows in #3, while maintaining the links to values in #1. How do I do this. I need a command functionally equivalent to copy/paste special values/transpose/link but in paste special values I can choose either transpose or link, but not both. Bill |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
2 Steps: 1. On worksheet #3 highlight the transpose range, type: =transpose(range2) and press cntrl&shift +Enter. range2= the range of the table on worksheet #2 to be transposed. 2. Put your feet up on the table and smile -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531894 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
One way:
Select the range to copy Edit|Replace what: = (equal sign) with: $$$$$ replace all Now your formulas are all text. With that range still selected edit|copy then select the new location edit|paste special|transpose Now select each range edit|replace what: $$$$$ with: = replace all And you've converted the text back to formulas. (Don't forget to do both the original range and the pasted range.) Bill wrote: I have worksheet #2 containing a table where all cells, including row and column headings, contain links to values in worksheet #1 in the workbook. I want to "transpose" the table in #2 (including row and column headings) into a table in worksheet #3, so that the rows in #2 become columns on #3 and the columns in #2 become rows in #3, while maintaining the links to values in #1. How do I do this. I need a command functionally equivalent to copy/paste special values/transpose/link but in paste special values I can choose either transpose or link, but not both. Bill -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
Very nice Dave -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531894 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
John James - thanks for the solution - knew it should be easy but could not
figure it out on my own. -- Bill "John James" wrote: 2 Steps: 1. On worksheet #3 highlight the transpose range, type: =transpose(range2) and press cntrl&shift +Enter. range2= the range of the table on worksheet #2 to be transposed. 2. Put your feet up on the table and smile -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531894 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
Dave
Thanks for your suggestion -- this actually solved an additinal delima for me at the same time. -- Bill "Dave Peterson" wrote: One way: Select the range to copy Edit|Replace what: = (equal sign) with: $$$$$ replace all Now your formulas are all text. With that range still selected edit|copy then select the new location edit|paste special|transpose Now select each range edit|replace what: $$$$$ with: = replace all And you've converted the text back to formulas. (Don't forget to do both the original range and the pasted range.) Bill wrote: I have worksheet #2 containing a table where all cells, including row and column headings, contain links to values in worksheet #1 in the workbook. I want to "transpose" the table in #2 (including row and column headings) into a table in worksheet #3, so that the rows in #2 become columns on #3 and the columns in #2 become rows in #3, while maintaining the links to values in #1. How do I do this. I need a command functionally equivalent to copy/paste special values/transpose/link but in paste special values I can choose either transpose or link, but not both. Bill -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transpose with linked data
Bill, Your feedback is appreciated. Cheers, -- John James ------------------------------------------------------------------------ John James's Profile: http://www.excelforum.com/member.php...o&userid=32690 View this thread: http://www.excelforum.com/showthread...hreadid=531894 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Microsoft query data into excel changes linked rows | Links and Linking in Excel | |||
I have a column of data and I'd like to transpose it to go across. | Excel Discussion (Misc queries) | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
How can I transpose data from a spreadsheet into a form that does. | Excel Worksheet Functions |