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

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

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

Merged cells are nothing but trouble!

Try this:

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

Copy across as needed.

Biff

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

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

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.


--
Regards

Roger Govier


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

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

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


--
Regards

Roger Govier


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

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

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


--
Regards

Roger Govier


"T. Valko" wrote in message
...
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" wrote in message
...
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.


--
Regards

Roger Govier


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

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

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

"Roger Govier" wrote in message
...
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.


--
Regards

Roger Govier


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