transpose absolute references

  • Thread starter Thread starter Mark Whittall
  • Start date Start date
M

Mark Whittall

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?
 
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
 
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 >
 
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?
 
Back
Top