How do I paste a link vertically?

[Jordan]

I have an order form for my company that uses a single line for each product
and has separate columns for size and quantity.
I need to copy the horizontal data from each line (5 cells with quantity
information) onto another worksheet but vertically (I need the data
vertically to enable easy importation into a database) The problem is:

If I copy cell number 1a-1e and paste vertically using the paste link
command the data is written as 1a,2a,3a,4a,5a referencing back to the wrong
cells. Any ideas?

I can paste each cell individually with no problem, but this is tedious over
the 3000 cells I have to link!

Cheers,
Jordan
nyroad...@earthlink.net

[Bernard REY]

Jordan wrote :

> I need to copy the horizontal data from each line (5 cells with quantity
> information) onto another worksheet but vertically (I need the data
> vertically to enable easy importation into a database) The problem is:
>
> If I copy cell number 1a-1e and paste vertically using the paste link
> command the data is written as 1a,2a,3a,4a,5a referencing back to the
> wrong cells. Any ideas?

If you paste this formula in cell A1, and then copy it down, I think you'll
have what you want to.

=INDIRECT("[TargetBook.xls]MySheet!"&CHAR(ROW(A1)+64)&COLUMN(A1))

But I'm not sure if that totally answers your question. And besides, I
suppose it can't work properly if you need to copy it over more than 26
lines

Let's have it a try, and then maybe you can see how it can be modified...

--
Bernard Rey - Toulouse

[Matthew Henson]

Jordan,

I have got good results using the TRANSPOSE function to solve similar problems.

Select the range where the results are to go, go to the formula bar and type =transpose( then select the source range. This is an array formula, so you need to enter it by pressing Command + Enter (rather than clicking the green tick or pressing Enter).

HTH, Matthew

>.
>