I have been asked to populate cells in a spreadsheet (Sheet1) with
data from another spreadsheet (Sheet2). Both sheets contain monthly
data. Sheet 1 has the monthly data running down the columns i.e.
Jan=Row1, Feb=Row2 etc. Sheet2 has the monthly data running across
the columns i.e. Jan=ColA, Feb=ColB etc.

The problem is as follows: When I link the first cell on Sheet 1 and
then try to drag the formula down to the other months, the formula
does not update as I want it to i.e. linking to the next cell
horizontally in Sheet 2. Obviously it links to the next cell
vertically in Sheet2.

Is there an operator that I can insert into the formula, so that when
I drag it vertically in Sheet1, it knows that I want data from the
next cell horizontally in Sheet2?

If there were only a few cells involved I'd just do it manually, but
there are thousands.

When you create the link, type the equal sign, then select the sheet and
cell. BEFORE pressing enter, press F4 until all the $ signs are removed
from the cell address.

HTH,
Bernie
MS Excel MVP


wrote in message
...
I have been asked to populate cells in a spreadsheet (Sheet1) with
data from another spreadsheet (Sheet2). Both sheets contain monthly
data. Sheet 1 has the monthly data running down the columns i.e.
Jan=Row1, Feb=Row2 etc. Sheet2 has the monthly data running across
the columns i.e. Jan=ColA, Feb=ColB etc.

The problem is as follows: When I link the first cell on Sheet 1 and
then try to drag the formula down to the other months, the formula
does not update as I want it to i.e. linking to the next cell
horizontally in Sheet 2. Obviously it links to the next cell
vertically in Sheet2.

Is there an operator that I can insert into the formula, so that when
I drag it vertically in Sheet1, it knows that I want data from the
next cell horizontally in Sheet2?

If there were only a few cells involved I'd just do it manually, but
there are thousands.

On Apr 11, 11:48*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
When you create the link, type the equal sign, then select the sheet and
cell. *BEFORE pressing enter, press F4 until all the $ signs are removed
from the cell address.

HTH,
Bernie
MS Excel MVP

Thanks for the reply Bernie, but the problem is still there. There
were no $ signs in the formula, but I redid the link following your
instructions anyway. When I drag the formula down in Sheet1 it wants
to copy the next cell down in Sheet2 instead of the next cell across
in Sheet2.

I guess what I'm really trying to do is to paste links but in a
transposed direction. Does Excel have this functionality?

I'm guessing you want something like this:

Sheet1A1 = Sheet2A1
Sheet1A2 = Sheet2B1
Sheet1A3 = Sheet2C1
Sheet1A4 = Sheet2D1

If so, enter a formula like this on Sheet1:

=INDEX(Sheet2!A$1:Z$1,ROWS(A$1:A1))

Copy down as needed. Adjust ranges to suit.

--
Biff
Microsoft Excel MVP


wrote in message
...
I guess what I'm really trying to do is to paste links but in a
transposed direction. Does Excel have this functionality?

On Apr 11, 1:18*pm, "T. Valko" wrote:
I'm guessing you want something like this:

Sheet1A1 = Sheet2A1
Sheet1A2 = Sheet2B1
Sheet1A3 = Sheet2C1
Sheet1A4 = Sheet2D1

If so, enter a formula like this on Sheet1:

=INDEX(Sheet2!A$1:Z$1,ROWS(A$1:A1))

Copy down as needed. Adjust ranges to suit.

--
Biff
Microsoft Excel MVP

Thanks Biff. That does exactly what I need it to. Much appreciated.