how do I link data between worksheets when data is transposed?

[The Pegster]

I have a wksheet with 217 rows of wx stations. I transposed the data for
each individual wx station to a more easily read column format. I would like
to link the data so that if I make a change on the original "row" wksheet, it
changes on the individual station wksheet. I can do it cell by cell
individually, but I'm thinking there must be a way to do it more efficiently?
When I try to "paste special", the box for "paste link" is no longer
selectable when I tell it to transpose the data....I am using Microsoft Excel
2007 with Window XP.

 

[T. Valko]

Maybe something like this...

On Sheet1 you have:

........A...
1.....x
2.....y
3.....z

On Sheet2 you want:

......A.....B.....C
1...x......y......z

Enter this formula on Sheet2 A1 and copy across to C1:

=INDEX(Sheet1!$A1:$A3,COLUMNS($A1:A1))

 

[The Pegster]

Well, I'm sure this may work, but i can't seem to copy it correctly? When
you say Sheet 1 and Sheet 2, am I supposed to call it that? or use the name
of the sheet?
When I follow your directions I get a circular reference error...
What I have exactly is on the second worksheet in my Book is the main data
(sheet name is ASCADS), consisting of 217 rows with 57 columns (A thru CE),
each row is the source for the transposed data that I have on individual
sheets. Sheet one of my book is other data. I hope this makes sense?
thanks for trying

 

[T. Valko]

(sheet name is ASCADS), consisting of 217

rows with 57 columns (A thru CE)

57 columns starting from column A would be A:BE.

So, on your other sheet you want to put that data in a 217 column by 57 row
block, right?

On your ASCADS sheet the range would be A1:BE217 (217 rows by 57 columns).

On the sheet where you want to transpose this data select a 217 column by 57
row block of cells. If the first cell is A1 then a 217 column by 57 row
block of cells would be the range A1:HI57

On the other sheet select the range A1:HI57 starting from cell A1.
Type the formula but *do not hit the enter key*

=TRANSPOSE(ASCADS!$A$1:$BE$217)

After you've typed the formula hold down both the CTRL key and the SHIFT key
then hit ENTER. This will enter the formula as an array.