copy formula down a column and have cell references change within formula

[brad]

I'm building a golf league spreadsheet that will hopefully make my
calculations each week easy. I've got some formulas that reference cells in
another worksheet.
=SUM('Weekly Input'!N3:N4)
When I tried to copy (drag) this formula down the column I get
=SUM('Weekly Input'!N4:N5).
"N3:N4" referece merged cells from my input worksheet. My worksheet this
formula is in has 18 columns with 16 rows. Is there a better way to
reference the merged input cells and also copy the information for ease of
setup.

(This is my input sheet) Col N
Team Player Score Weekly Score
1 Player 1 N3:N4
Player 2
2 Player 3 N5:N6
Player 4


I need the information in N3:N4 to show up in worksheet "Weekly scores" A1;
N5:N6 to B1, etc. there are 16 teams on the "input" worksheet and 16 rows no
the "weekly scores" worksheet.

I've tried to "copy" and "paste special" with "paste link" but that doesn't
seem to work either for copying down. Is there a faster way of inputing a
formula or reference without doing each cell one at a time?
Brad

 

[T. Valko]

Merged cells are nothing but trouble!

Try this:

=OFFSET('Weekly Input'!$N3,(COLUMNS($A:A)-1)*2,0)

Copy across as needed.

Biff

 

[Roger Govier]

Hi brad

Merged cells only hold data in the top left cell of the merged area,
even though it displays in the bottom right corner of the merged area by
default.
Therefore there is no need for SUM(N3:N4), it would just be N3.
Supposing N3 contained 5, and N5 contained 6, and you entered in B1
=N3, B1 would show 5
If you are copying down through column B which does not contain merged
cells, the formula would become =N4, =N5 etc. and you would see 5, 0, 6
respectively

To overcome the problem, use
=INDIRECT("'Weekly Input'!N"&ROW(N2)*2-1)
(Note there is still a single quote following the opening double quote "
' )

Row(N2) returns 2, which gets stepped up by 1 as you copy down the
column so it would become
2*2-1 =3
3*2-1 =5
and therefore pull the values from N3, N5 etc as you proceed down the
column.

 

[T. Valko]

The subject line says:

copy formula down a column...

In the actual post it says:

I need the information in N3:N4 to show up in worksheet "Weekly scores" A1;
N5:N6 to B1, etc.

I went with the post.

Biff

 

[Roger Govier]

Hi Biff

As you rightly say, there is a confusion in what is required.
So the OP can choose
=OFFSET('Weekly Input'!$N3,(COLUMNS($A:A)-1)*2,0)

=INDIRECT("'Weekly Input'!N"&COLUMN(B1)*2-1)
for columns

or
=OFFSET('Weekly Input'!$N3,(ROWS($A:A)-1)*2,0)

=INDIRECT("'Weekly Input'!N"&ROW(N2)*2-1)
for rows

 

[brad]

Roger,

Thank You, that seems to be doing the trick. This is getting more involved
that I thought. Also Thank You for going by my subject line and not my
example I gave. I do need to "copy down" and my exampe of N5:N6 to B1 should
have been "to A2", sorry, but you gave me the correct answer to what I need.
I may have other questions. I trying to build this for this years league and
future league mgrs. and ease of use. It's a small church league and we don't
have the funds to purchase a "golf league" software pkg.
Again, Thank You very much
Brad