transpose absolute references
 

I have a spreadsheet with 27 columns, consisting of
names, and 26 other items particular to that name. In
another sheet (within the same file) I would like the
same information to appear, but transposed; I would end
up with a sheet with 27 rows and an undetermined number
of columns (one for each name). his is to facilitate
printing indiidual records.

I need to come up with a method by which, after I add
records to the first sheet (27 columns), I can copy it to
the 2nd (27 rows) without copying cell references one at
a time. Any ideas?

transpose absolute references
 

Hi Kiat,

Trouble is, I would like to avoid the manual transpose
paste every time I enter a new record on the first page.
Is there a way to set up the 2nd page with formulae, so
that :

1) If I change an existing record on the first page, the
corresponding data on the 2nd page will also change, and
2) If I enter a new record on the first page, I can
duplicate it on the second page by copying a column to
the next (empty) column

(1) and (2) without having to paste/transpose each time,
or more important, remembering to do so when I change
existing records
-----Original Message-----
After yiou click the copy button for the range, goto the
2nd sheet, and
click on menu Edit/Paste Special, a dialog will show up,
select the checkbox
Transpose, which is the last item in my version, and
paste it.

"Mark Whittall" wrote in
message
...
I have a spreadsheet with 27 columns, consisting of
names, and 26 other items particular to that name. In
another sheet (within the same file) I would like the
same information to appear, but transposed; I would end
up with a sheet with 27 rows and an undetermined number
of columns (one for each name). his is to facilitate
printing indiidual records.

I need to come up with a method by which, after I add
records to the first sheet (27 columns), I can copy it
to
the 2nd (27 rows) without copying cell references one
at
a time. Any ideas?


.

transpose absolute references
 

Mark,

following should work, though you may want to suppress zero -display
or embelish the formula to account for empty cells.

sheet2:
in a1 =transpose(sheet1!A1:X256)
select Rows!!! 1 thru 24
press F2
press CTRL-SHIFT-ENTER

this should result in 1 big array.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Mark Whittall" wrote:

Hi Kiat,

Trouble is, I would like to avoid the manual transpose
paste every time I enter a new record on the first page.
Is there a way to set up the 2nd page with formulae, so
that :

1) If I change an existing record on the first page, the
corresponding data on the 2nd page will also change, and
2) If I enter a new record on the first page, I can
duplicate it on the second page by copying a column to
the next (empty) column

(1) and (2) without having to paste/transpose each time,
or more important, remembering to do so when I change
existing records
-----Original Message-----
After yiou click the copy button for the range, goto the
2nd sheet, and
click on menu Edit/Paste Special, a dialog will show up,
select the checkbox
Transpose, which is the last item in my version, and
paste it.

"Mark Whittall" wrote in
message
...
I have a spreadsheet with 27 columns, consisting of
names, and 26 other items particular to that name. In
another sheet (within the same file) I would like the
same information to appear, but transposed; I would end
up with a sheet with 27 rows and an undetermined number
of columns (one for each name). his is to facilitate
printing indiidual records.

I need to come up with a method by which, after I add
records to the first sheet (27 columns), I can copy it
to
the 2nd (27 rows) without copying cell references one
at
a time. Any ideas?


.

transpose absolute references
 

Worked very nicely, thank you. I discovered that if I
insert a row anywhere in my first sheet before the last
row, that a new column will appear in the array in the
right place.

One other thing I didn't try - if I re-sort the data in
the first sheet on another field, what will happen to my
array on sheet 2?


-----Original Message-----
Mark,

following should work, though you may want to suppress
zero -display
or embelish the formula to account for empty cells.

sheet2:
in a1 =transpose(sheet1!A1:X256)
select Rows!!! 1 thru 24
press F2
press CTRL-SHIFT-ENTER

this should result in 1 big array.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Mark Whittall" wrote:

Hi Kiat,

Trouble is, I would like to avoid the manual transpose
paste every time I enter a new record on the first
page.
Is there a way to set up the 2nd page with formulae,
so
that :

1) If I change an existing record on the first page,
the
corresponding data on the 2nd page will also change,
and
2) If I enter a new record on the first page, I can
duplicate it on the second page by copying a column to
the next (empty) column

(1) and (2) without having to paste/transpose each
time,
or more important, remembering to do so when I change
existing records
-----Original Message-----
After yiou click the copy button for the range, goto
the
2nd sheet, and
click on menu Edit/Paste Special, a dialog will show
up,
select the checkbox
Transpose, which is the last item in my version, and
paste it.

"Mark Whittall" wrote in
message
.. .
I have a spreadsheet with 27 columns, consisting of
names, and 26 other items particular to that name.
In
another sheet (within the same file) I would like the
same information to appear, but transposed; I would
end
up with a sheet with 27 rows and an undetermined
number
of columns (one for each name). his is to facilitate
printing indiidual records.

I need to come up with a method by which, after I add
records to the first sheet (27 columns), I can copy
it
to
the 2nd (27 rows) without copying cell references
one
at
a time. Any ideas?


.



.