How do i copy rows to columns on separate sheet, and have them upd

[Guest]

Hi all - I hope someone can help me with this trivial question!

I have a spreadsheet, Sheet1, where ~25 records each have many fields. Each
record is on a separate line; eg

Record Packaging Materials Labour
Australia £25 £30 £10
USA £10 £20 £40 etc.

I would like to 'copy' this data to a separate sheet (Sheet2, Sheet3 etc)
for each record, but this time having the fields go down the page. eg

Australia
Packaging £25
Materials £30
Labour £10

I require the use of a formula so that changes on sheet 1 are updated
automatically. It is easy to do this for a single cell, however when i copy
the =Sheet1 B2 downwards, obviously it refers to B3, B4 etc, rather than C2,
D2 etc as I require.

I have a feeling this is very simple!

 

[David Biddulph]

To link from a row to a column, use the TRANSPOSE() formula. If you want to
automate doing this across a number of sheets, that might need some VBA.

 

[Lori]

It's not totally straightforward since you cannot use Edit > Paste
Special with both transpose and paste link. Instead you could try
transposing references and then evaluating:

1. Copy sheet1 range and Paste Special to new sheet with Paste link
2. Edit > Replace Find: *! Replace: !
3. Copy selection and Paste special to new range with tanspose
4. Edit > Replace Find: ! Replace: =Sheet1!