Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How to control cell references when copying a formula?

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.

Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,574
Default How to control cell references when copying a formula?

You're talking about absolute versus relative references. Absolute
references anchor the reference and don't change it as the formula is copied;
relative references change as the formula is copied.

In your example, if you want a formula always to sum E1:G1, write it like
this: =SUM($E$1:$G$1) The $ signs indicate absolute references; E1:G1 is a
relative reference.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"rmmshost" wrote:

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.

Thanks!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default How to control cell references when copying a formula?

the $ locks the row or column when copying. There is 4 ways of referencing a
cell. a formula can contain many celss refereces, wher some will have $ and
other won't.

A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes

"rmmshost" wrote:

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.

Thanks!

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How to control cell references when copying a formula?

Dave and Joel,

Thanks very much for the fast response but that's not what I'm referring to.
I want the opposite of anchoring/absolute references, I want to cell
references to CHANGE when I copy it over. If I copy the formula over to the
next column it also moves the cell reference one column, but I need a way for
the reference to move more than one column.

Have a look at my original example and let me know if I need to explain
anything further.

Thanks!


"Joel" wrote:

the $ locks the row or column when copying. There is 4 ways of referencing a
cell. a formula can contain many celss refereces, wher some will have $ and
other won't.

A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes

"rmmshost" wrote:

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.

Thanks!

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 9,101
Default How to control cell references when copying a formula?

I don't know how well you know excel. but I have don't similar thing by
using the offet function, row(), or column() functtions

if you want to duble you row spacing. You can do something like this

offset(c1, 2 * (row(A3) - row(A1)),0)

"rmmshost" wrote:

Dave and Joel,

Thanks very much for the fast response but that's not what I'm referring to.
I want the opposite of anchoring/absolute references, I want to cell
references to CHANGE when I copy it over. If I copy the formula over to the
next column it also moves the cell reference one column, but I need a way for
the reference to move more than one column.

Have a look at my original example and let me know if I need to explain
anything further.

Thanks!


"Joel" wrote:

the $ locks the row or column when copying. There is 4 ways of referencing a
cell. a formula can contain many celss refereces, wher some will have $ and
other won't.

A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes

"rmmshost" wrote:

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.

Thanks!



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 620
Default How to control cell references when copying a formula?

Put
=SUM(OFFSET($A1,0,3*(COLUMN()-COLUMN($I1)),1,3))
into I1, and copy into J1.
--
David Biddulph

"rmmshost" wrote in message
...
Dave and Joel,

Thanks very much for the fast response but that's not what I'm referring
to.
I want the opposite of anchoring/absolute references, I want to cell
references to CHANGE when I copy it over. If I copy the formula over to
the
next column it also moves the cell reference one column, but I need a way
for
the reference to move more than one column.

Have a look at my original example and let me know if I need to explain
anything further.

Thanks!


"Joel" wrote:

the $ locks the row or column when copying. There is 4 ways of
referencing a
cell. a formula can contain many celss refereces, wher some will have $
and
other won't.

A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes

"rmmshost" wrote:

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3,
so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I
copy
over this formula to the next cell, J1, because I want to sum the other
3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one
column)
the cell reference in the formula also moves only one column and ends
up
trying to sum B1:D1. Of course, this is working as it should but I
really
need to find a way to make the cell reference refer to E1:G1 when I
copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications
on
what I'm trying to do.

Thanks!



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default How to control cell references when copying a formula?

Perhaps the simplest way is instead of copying the formula from I1 to
J1 you should copy it to L1 instead. Then you can either CUT/Paste the
formula from L1 into J1 or you can delete the cells between I1 and L1
to get the two formulae next to each other.

You can use Offset to do this, but the method I describe is very
quick.

Hope this helps.

Pete

On Feb 23, 6:56 pm, rmmshost
wrote:
Dave and Joel,

Thanks very much for the fast response but that's not what I'm referring to.
I want the opposite of anchoring/absolute references, I want to cell
references to CHANGE when I copy it over. If I copy the formula over to the
next column it also moves the cell reference one column, but I need a way for
the reference to move more than one column.

Have a look at my original example and let me know if I need to explain
anything further.

Thanks!



"Joel" wrote:
the $ locks the row or column when copying. There is 4 ways of referencing a
cell. a formula can contain many celss refereces, wher some will have $ and
other won't.


A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes


"rmmshost" wrote:


Hi,


I'm not even sure how to describe this problem, so bear with me ;-)


Here's a (hopefully) clear example of what I'm trying to do:


I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.


Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)


Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.


Thanks!- Hide quoted text -


- Show quoted text -



  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How to control cell references when copying a formula?

thanks david and joel, i had found the offset function but wasn't confident
that that would help. looks like it's the best way to go. thanks again!

"David Biddulph" wrote:

Put
=SUM(OFFSET($A1,0,3*(COLUMN()-COLUMN($I1)),1,3))
into I1, and copy into J1.
--
David Biddulph

"rmmshost" wrote in message
...
Dave and Joel,

Thanks very much for the fast response but that's not what I'm referring
to.
I want the opposite of anchoring/absolute references, I want to cell
references to CHANGE when I copy it over. If I copy the formula over to
the
next column it also moves the cell reference one column, but I need a way
for
the reference to move more than one column.

Have a look at my original example and let me know if I need to explain
anything further.

Thanks!


"Joel" wrote:

the $ locks the row or column when copying. There is 4 ways of
referencing a
cell. a formula can contain many celss refereces, wher some will have $
and
other won't.

A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes

"rmmshost" wrote:

Hi,

I'm not even sure how to describe this problem, so bear with me ;-)

Here's a (hopefully) clear example of what I'm trying to do:

I'm working with 6 columns of data which are divided into groups of 3,
so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I
copy
over this formula to the next cell, J1, because I want to sum the other
3
cells, E1:G1.

Here's the issue: When I copy over the formula from I1 to J1 (one
column)
the cell reference in the formula also moves only one column and ends
up
trying to sum B1:D1. Of course, this is working as it should but I
really
need to find a way to make the cell reference refer to E1:G1 when I
copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)

Can anyone help with this? Let me know if you need any clarifications
on
what I'm trying to do.

Thanks!




  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default How to control cell references when copying a formula?

thanks pete, when i'm only dealing with a couple of calculations manually
changing the references is no big deal, but as it grows to dozens of refs it
gets a bit more daunting (and annoying! ;-)

"Pete_UK" wrote:

Perhaps the simplest way is instead of copying the formula from I1 to
J1 you should copy it to L1 instead. Then you can either CUT/Paste the
formula from L1 into J1 or you can delete the cells between I1 and L1
to get the two formulae next to each other.

You can use Offset to do this, but the method I describe is very
quick.

Hope this helps.

Pete

On Feb 23, 6:56 pm, rmmshost
wrote:
Dave and Joel,

Thanks very much for the fast response but that's not what I'm referring to.
I want the opposite of anchoring/absolute references, I want to cell
references to CHANGE when I copy it over. If I copy the formula over to the
next column it also moves the cell reference one column, but I need a way for
the reference to move more than one column.

Have a look at my original example and let me know if I need to explain
anything further.

Thanks!



"Joel" wrote:
the $ locks the row or column when copying. There is 4 ways of referencing a
cell. a formula can contain many celss refereces, wher some will have $ and
other won't.


A1 - when copied both the column and row changes
$A1 - Only the row changes, not the column
A$1 - Only the column changes, now the row
$A$1 - Neither the Row or Columns changes


"rmmshost" wrote:


Hi,


I'm not even sure how to describe this problem, so bear with me ;-)


Here's a (hopefully) clear example of what I'm trying to do:


I'm working with 6 columns of data which are divided into groups of 3, so
there is data in A1:C1 and E1:G1. In cell I1, I sum A1:C1 and THEN I copy
over this formula to the next cell, J1, because I want to sum the other 3
cells, E1:G1.


Here's the issue: When I copy over the formula from I1 to J1 (one column)
the cell reference in the formula also moves only one column and ends up
trying to sum B1:D1. Of course, this is working as it should but I really
need to find a way to make the cell reference refer to E1:G1 when I copy it
over. (By the way, I'm providing this extremely simplified version for
demonstration purposes.)


Can anyone help with this? Let me know if you need any clarifications on
what I'm trying to do.


Thanks!- Hide quoted text -


- Show quoted text -




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying formulas, relative and absolute cell references Garnet Excel Discussion (Misc queries) 3 February 22nd 07 08:51 AM
Auto-increment data source cell references when copying charts Dave Excel Discussion (Misc queries) 2 July 5th 06 07:45 PM
How to auto-increment data source cell references when copying cha Dave Charts and Charting in Excel 0 June 28th 06 05:41 AM
copying sheet references that refer to a cell in the preceding she GBT Excel Worksheet Functions 1 March 24th 06 07:51 PM
Copying linked cell references. Shams Excel Worksheet Functions 0 February 3rd 05 01:53 PM


All times are GMT +1. The time now is 07:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"