Help Needed With Copying Formulas

[cqdigital]

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.

 

[Bernie Deitrick]

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

 

[cqdigital]

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.

 

[cqdigital]

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

 

[T. Valko]

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.

 

[cqdigital]

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.

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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.

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