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!

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.
--
David Biddulph

"Gre" wrote in message
...
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!

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!


Gre wrote:

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!