How to copy and paste from a row to a column, that auto updates....

[jgeitner]

Hi all

Wondering if someone can help.

I've a workbook with a couple of worksheets in it, where one worksheet has a column of data where I want to link the data into a row on another worksheet, however I'm having challenges and really can't work it out.

I can drag the formula as it just copies to a row - I've tried transposing it but it goes to totally different cells which I don't know why.

Any helps appreciated.

 

[sheetspread]

Use the transpose formula (with control shift enter) or index.

 

[jgeitner]

Thanks for your advice.

For some reason the Paste Special doesn't even work on this sheet. I only get Paste as Unicode Text or Text....

Why's this happening?!

I tried control shift enter but it just copies it as a row from that sheet to the row on the other worksheet, not from a column to a row.

So I need to take the numbers from May 1 to 31, down these columns here ~


2012
May June July August September October November December
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
3 89 0 0 0 0 0 0 0
4 0 0 0 0 0 0 0 0
5 0 0 0 0 0 0 0 0
6 0 0 0 0 0 0 0 0
7 0 0 0 0 0 0 0 0
8 0 0 0 0 0 0 0 0
9 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 0 0
11 0 0 0 0 0 0 0 0
12 0 0 0 0 0 0 0 0
13 0 0 0 0 0 0 0 0
14 0 0 0 0 0 0 0 0
15 0 0 0 0 0 0 0 0
16 0 0 0 0 0 0 0 0
17 89 0 0 0 0 0 0 0
18 0 0 0 0 0 0 0 0
19 0 0 0 0 0 0 0 0
20 0 0 0 0 0 0 0 0
21 0 0 0 0 0 0 0 0
22 0 0 0 0 0 0 0 0
23 0 0 0 0 0 0 0 0
24 0 0 0 0 0 0 0 0
25 0 0 0 0 0 0 0 0
26 0 0 0 0 0 0 0 0
27 0 0 0 0 0 0 0 0
28 0 0 0 0 0 0 0 0
29 0 0 0 0 0 0 0 0
30 0 0 0 0 0 0 0 0
31 0 0 0 0 0

and link where presently its zero's to this on a separate worksheet here ~

if this makes any sense

May-12 1 2 3 4 5 6 7 8 9...
Tue Wed Thu Fri Sat Sun Mon Tue Wed...
Major Event
Minor Event
Holiday
Transient OTB 0 0 0 0 0 0 0 0 0
Total Rms OTB 0 0 0 0 0 0 0 0 0
Occ% OTB 0% 0% 0% 0% 0% 0% 0% 0% 0%

I'm quite confused.

 

[jgeitner]

Sorry, just FYI, those Zero's are supposed to be beneath each month!

And its the month's that are then in columns on this other sheet.

But I can't even do a paste special for some reason. Not sure if its my laptop set up! Or something in Excel.

 

[Jonmo1]

Using the transpose formula is tricky, but it works when done correctly.

You can't enter the transpose in 1 cell, then drag it accross...
You have to first select your destination range...the entire destination range.
It should be the same size but opposite shape as the source.

so if you're copying A1:A10, to another sheet A1:J1
You highlight A1:J1
Then type
=TRANSPOSE('Source sheet'!A1:A10) <--DO NOT PRESS ENTER
Press CTRL + SHIFT + ENTER..

Example below..Hope it helps.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	a		a	b	c	d	e	f	g	h	i	j
2	b
3	c
4	d
5	e
6	f
7	g
8	h
9	i
10	j
Sheet1

 

[sheetspread]

Index might be better, unlike a CSE transpose you don't get the "can't change part of an array" error.

It sounds like a pivot table is the best solution for this case, i.e. a crosstab query.

Has this sheet been loaded from an external database? There may be named ranges which hamper paste special and other functions.

 

[jgeitner]

Hey thanks everyone.

So its a little weird; I've taken the spreadshee.t to my personal laptop, which is also HP and this enables the paste special function to work.

Must be something on the laptop enabled or disabled - my personal laptop did open up and say "enable editing"

I tried the Transpose formula and it works awesomely - thanks so much. I did get that array error thing but by transposing it into a line, and adding a formula into the row below and just hiding that row, I've made it work awesomely. You can't actually make changes to the cell using transpose - even so much as making text center in a cell, which is a little annoying, but at least the numbers are there and I don't have to go cell by cell doing the "=" bit.

I'm not good with Pivotal Tables - I tried them but just get a total sum of all those cells! I may read up some more on piv tables as it may work, but transpose works for the moment!

Thanks for your help, its much appreciated. You guys are whizzes at this stuff!

Jonathan