UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Transpose Data, Office 2010    
 
   
Larry Larsen
post Oct 11 2018, 09:32 AM
Post#1


UA Editor + Utterly Certified
Posts: 24,191
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

I have a "row" of values that I currently C&P to a different sheet within the workbook..

Copy A to B

eg:
12|14|6|66|22|etc...

I copy the above A into a worksheet B, I then "Transpose" this data into a "column"..

To make the process simpler I make a reference in "B" to =ValueOfA1... etc...
eg:
= Sheet 1!A1 | = Sheet 1!B1 | = Sheet 1!C1 | etc....

What I'm unable to do is then transpose the values.. all I get is the reference...
eg:
Sheet 1!A1
Sheet 1!B1
Sheet 1!C1
etc...
Am I led to believe I can't transpose value/references..??

Any help would be appreciated..
iconfused.gif
Reason for edit: edit cell references..

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
Debaser
post Oct 11 2018, 09:39 AM
Post#2



Posts: 7
Joined: 11-October 18



I'm confused by the description. A1, A2 etc would be a column, not a row.

If you need to transpose from a row to a column (or vice versa), you could use an INDEX formula like this:

(row to column)

=INDEX(Sheet1!1:1,1,ROWS($Z$1:$Z1))
and copy down. For column to row, use:

=INDEX(Sheet1!A:A,COLUMNS($Z$1:Z$1))

The ROWS/COLUMNS parts are just there to return an incrementing counter 1, 2, 3 etc .

You can also use the TRANSPOSE function array entered into multiple cells if you have a fixed size range to transpose.
This post has been edited by Debaser: Oct 11 2018, 09:56 AM
Go to the top of the page
 
Larry Larsen
post Oct 11 2018, 11:22 AM
Post#3


UA Editor + Utterly Certified
Posts: 24,191
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Many thanks for your quick reply, and sorry about the referencing of my cells..

May be a small image of what happening when I try to transpose from a cell that hold a reference link..

The data will change very week, so I transpose into incremented weekly cells as shown on the image..

Image attached..
thumbup.gif
Attached File(s)
Attached File  TransposeIssue.jpg ( 91.06K )Number of downloads: 8
 

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
River59
post Oct 11 2018, 02:47 PM
Post#4



Posts: 1,407
Joined: 7-April 10
From: Detroit, MI


Larry, I am sorry that I just can't free up a few minutes to work this out for you but here is what I think you need to do:


You can find a range in Excel. Find the first and last column on SheetA. Then copy this range, add a sheetb , paste special/transpose.

Range(The range found on SheetA).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Sheets("Sheet3").Name = "SheetB"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("J24").Select

Sorry I can't write this for you.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
River59
post Oct 11 2018, 04:11 PM
Post#5



Posts: 1,407
Joined: 7-April 10
From: Detroit, MI


Larry, I am sorry that I just can't free up a few minutes to work this out for you but here is what I think you need to do:


You can find a range in Excel. Find the first and last column on SheetA. Then copy this range, add a sheetb , paste special/transpose.

Range(The range found on SheetA).Select
Selection.Copy
Sheets.Add After:=ActiveSheet
Sheets("Sheet3").Name = "SheetB"
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("J24").Select

Sorry I can't write this for you.

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
Larry Larsen
post Oct 12 2018, 08:51 AM
Post#6


UA Editor + Utterly Certified
Posts: 24,191
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
Many thanks.. I can work with that...
thumbup.gif notworthy.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
River59
post Oct 12 2018, 12:02 PM
Post#7



Posts: 1,407
Joined: 7-April 10
From: Detroit, MI


Glad I could offer a little bit of help even if it was just a push in the right direction. Good luck!
This post has been edited by River59: Oct 12 2018, 12:02 PM

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th October 2018 - 12:19 AM