Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I change cell references in a column | Excel Worksheet Functions | |||
copy a formula that does not change the column or row number? | Excel Discussion (Misc queries) | |||
How do I change column references when filling down a formula | Excel Discussion (Misc queries) | |||
how do i copy a formula when cell references r not together | New Users to Excel | |||
How do I copy cell values (derived from formula), not references? | Excel Worksheet Functions |