Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I link data between worksheets when data is transposed?
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I link data between worksheets when data is transposed?
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)) -- Biff Microsoft Excel MVP "The Pegster" <The wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I link data between worksheets when data is transposed?
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" wrote: 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)) -- Biff Microsoft Excel MVP "The Pegster" <The wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I link data between worksheets when data is transposed?
(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. -- Biff Microsoft Excel MVP "The Pegster" wrote in message ... 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" wrote: 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)) -- Biff Microsoft Excel MVP "The Pegster" <The wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link transposed data from one sheet to another for DB purposes | Excel Worksheet Functions | |||
link data to worksheets | Excel Worksheet Functions | |||
How to insert rows in transposed data table? | Excel Worksheet Functions | |||
Link Data from Multiple Worksheets | Excel Worksheet Functions | |||
Put same data in cells on two different worksheets (link?) | Setting up and Configuration of Excel |