+ Reply to Thread
Results 1 to 7 of 7

A "relative" absolute cell reference?

  1. #1
    pfrank
    Guest

    A "relative" absolute cell reference?

    I have a several rows of formulae that refer to a constant at the upper left
    corner of the range of formulae. I use an absolute cell reference to refer
    to the constant, and many relative relative references in rows of formulae.
    I want to copy this range of work and paste it several rows below the
    original. However, I want to use a different constant for the second range
    of formulae. Since I used an absolute reference in my original work, the
    pasted formulae still refer to my original constant. How can I make the
    reference to the original constant be absolute, yet relative to each range of
    formulae so I can change the constant each time I paste the range of
    formulae? For example:

    A1=7
    B2=100, C2=B2+A$1
    B3=105, C3=B3+A$1
    B4=110, C4=B4+A$1

    Now, copy this range A1:C4 and paste down at A15, now we have:

    A15=7
    B16=100, C16=B16+A$1
    B17=105, C17=B17+A$1
    B18=110, C18=B18+A$1

    However, what I really wanted was for A$15 to be my new constant (so I could
    change the number in that cell) for this new range of formulae. My actual
    formulae are much more numerous and complicated that this and I am trying not
    to have to go in and manually change each cell reference for the constant on
    my new range. It seems like I need some kind of formula in my original
    constant cell reference (A$1) that will allow it to change when it is pasted
    elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    make them work. Many thanks in advance for your thoughts.

  2. #2
    JLatham
    Guest

    RE: A "relative" absolute cell reference?

    Try this for the formula in C2:
    =B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))

    extend that down through the first group. Now when you copy the entire
    group and move it to another location, just change the ROW(A$1) at the top
    C-column cell in the new group as required and again just drag the formula
    down to the bottom of the new group. That was quick and dirty solution I
    came up with real fast. May even be a better solution, and if so, someone
    else will probably post it shortly.

    "pfrank" wrote:

    > I have a several rows of formulae that refer to a constant at the upper left
    > corner of the range of formulae. I use an absolute cell reference to refer
    > to the constant, and many relative relative references in rows of formulae.
    > I want to copy this range of work and paste it several rows below the
    > original. However, I want to use a different constant for the second range
    > of formulae. Since I used an absolute reference in my original work, the
    > pasted formulae still refer to my original constant. How can I make the
    > reference to the original constant be absolute, yet relative to each range of
    > formulae so I can change the constant each time I paste the range of
    > formulae? For example:
    >
    > A1=7
    > B2=100, C2=B2+A$1
    > B3=105, C3=B3+A$1
    > B4=110, C4=B4+A$1
    >
    > Now, copy this range A1:C4 and paste down at A15, now we have:
    >
    > A15=7
    > B16=100, C16=B16+A$1
    > B17=105, C17=B17+A$1
    > B18=110, C18=B18+A$1
    >
    > However, what I really wanted was for A$15 to be my new constant (so I could
    > change the number in that cell) for this new range of formulae. My actual
    > formulae are much more numerous and complicated that this and I am trying not
    > to have to go in and manually change each cell reference for the constant on
    > my new range. It seems like I need some kind of formula in my original
    > constant cell reference (A$1) that will allow it to change when it is pasted
    > elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    > make them work. Many thanks in advance for your thoughts.


  3. #3
    pfrank
    Guest

    RE: A "relative" absolute cell reference?

    While I think you are heading in the right direction, I would still need to
    change the ROW A$1 reference and copy down in my new group. However, my
    actual worksheet is quite involved (about 300 rows) and I cannot copy down
    the entire column since there are many rows of formulae that I don't want
    written over. But, like I said, I think you are on the right track here, and
    I am experimenting with OFFSET, ROW and COLUMN. Thanks!



    "JLatham" wrote:

    > Try this for the formula in C2:
    > =B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))
    >
    > extend that down through the first group. Now when you copy the entire
    > group and move it to another location, just change the ROW(A$1) at the top
    > C-column cell in the new group as required and again just drag the formula
    > down to the bottom of the new group. That was quick and dirty solution I
    > came up with real fast. May even be a better solution, and if so, someone
    > else will probably post it shortly.
    >
    > "pfrank" wrote:
    >
    > > I have a several rows of formulae that refer to a constant at the upper left
    > > corner of the range of formulae. I use an absolute cell reference to refer
    > > to the constant, and many relative relative references in rows of formulae.
    > > I want to copy this range of work and paste it several rows below the
    > > original. However, I want to use a different constant for the second range
    > > of formulae. Since I used an absolute reference in my original work, the
    > > pasted formulae still refer to my original constant. How can I make the
    > > reference to the original constant be absolute, yet relative to each range of
    > > formulae so I can change the constant each time I paste the range of
    > > formulae? For example:
    > >
    > > A1=7
    > > B2=100, C2=B2+A$1
    > > B3=105, C3=B3+A$1
    > > B4=110, C4=B4+A$1
    > >
    > > Now, copy this range A1:C4 and paste down at A15, now we have:
    > >
    > > A15=7
    > > B16=100, C16=B16+A$1
    > > B17=105, C17=B17+A$1
    > > B18=110, C18=B18+A$1
    > >
    > > However, what I really wanted was for A$15 to be my new constant (so I could
    > > change the number in that cell) for this new range of formulae. My actual
    > > formulae are much more numerous and complicated that this and I am trying not
    > > to have to go in and manually change each cell reference for the constant on
    > > my new range. It seems like I need some kind of formula in my original
    > > constant cell reference (A$1) that will allow it to change when it is pasted
    > > elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    > > make them work. Many thanks in advance for your thoughts.


  4. #4
    JLatham
    Guest

    RE: A "relative" absolute cell reference?

    I looked at it some more and the only constant is the A$1 reference that
    needs to be changed at the start of each group, then you just copy that down
    as far as the end of the whole group you just pasted. Let me think some more
    about making that a variable self-adjusting value. I was close a few minutes
    ago while taking the second look, but not close enough.

    "pfrank" wrote:

    > While I think you are heading in the right direction, I would still need to
    > change the ROW A$1 reference and copy down in my new group. However, my
    > actual worksheet is quite involved (about 300 rows) and I cannot copy down
    > the entire column since there are many rows of formulae that I don't want
    > written over. But, like I said, I think you are on the right track here, and
    > I am experimenting with OFFSET, ROW and COLUMN. Thanks!
    >
    >
    >
    > "JLatham" wrote:
    >
    > > Try this for the formula in C2:
    > > =B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))
    > >
    > > extend that down through the first group. Now when you copy the entire
    > > group and move it to another location, just change the ROW(A$1) at the top
    > > C-column cell in the new group as required and again just drag the formula
    > > down to the bottom of the new group. That was quick and dirty solution I
    > > came up with real fast. May even be a better solution, and if so, someone
    > > else will probably post it shortly.
    > >
    > > "pfrank" wrote:
    > >
    > > > I have a several rows of formulae that refer to a constant at the upper left
    > > > corner of the range of formulae. I use an absolute cell reference to refer
    > > > to the constant, and many relative relative references in rows of formulae.
    > > > I want to copy this range of work and paste it several rows below the
    > > > original. However, I want to use a different constant for the second range
    > > > of formulae. Since I used an absolute reference in my original work, the
    > > > pasted formulae still refer to my original constant. How can I make the
    > > > reference to the original constant be absolute, yet relative to each range of
    > > > formulae so I can change the constant each time I paste the range of
    > > > formulae? For example:
    > > >
    > > > A1=7
    > > > B2=100, C2=B2+A$1
    > > > B3=105, C3=B3+A$1
    > > > B4=110, C4=B4+A$1
    > > >
    > > > Now, copy this range A1:C4 and paste down at A15, now we have:
    > > >
    > > > A15=7
    > > > B16=100, C16=B16+A$1
    > > > B17=105, C17=B17+A$1
    > > > B18=110, C18=B18+A$1
    > > >
    > > > However, what I really wanted was for A$15 to be my new constant (so I could
    > > > change the number in that cell) for this new range of formulae. My actual
    > > > formulae are much more numerous and complicated that this and I am trying not
    > > > to have to go in and manually change each cell reference for the constant on
    > > > my new range. It seems like I need some kind of formula in my original
    > > > constant cell reference (A$1) that will allow it to change when it is pasted
    > > > elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    > > > make them work. Many thanks in advance for your thoughts.


  5. #5
    JLatham
    Guest

    RE: A "relative" absolute cell reference?

    The next best thing I can think of is to use Replace. Copy a group from one
    location on the worksheet to the new location. Note where the constant value
    (A1 = 7) was in the old group and where it now appears in the new group.

    With the entire group still selected use Edit | Replace to change (A$1) to
    the new location, as (A$12) and choose Replace All. Since that's the only
    place in any of the cells where I see a $ symbol, then it should give you no
    problems at all if everything else in the group of cells copied and pasted is
    as you've described here.


    "pfrank" wrote:

    > While I think you are heading in the right direction, I would still need to
    > change the ROW A$1 reference and copy down in my new group. However, my
    > actual worksheet is quite involved (about 300 rows) and I cannot copy down
    > the entire column since there are many rows of formulae that I don't want
    > written over. But, like I said, I think you are on the right track here, and
    > I am experimenting with OFFSET, ROW and COLUMN. Thanks!
    >
    >
    >
    > "JLatham" wrote:
    >
    > > Try this for the formula in C2:
    > > =B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))
    > >
    > > extend that down through the first group. Now when you copy the entire
    > > group and move it to another location, just change the ROW(A$1) at the top
    > > C-column cell in the new group as required and again just drag the formula
    > > down to the bottom of the new group. That was quick and dirty solution I
    > > came up with real fast. May even be a better solution, and if so, someone
    > > else will probably post it shortly.
    > >
    > > "pfrank" wrote:
    > >
    > > > I have a several rows of formulae that refer to a constant at the upper left
    > > > corner of the range of formulae. I use an absolute cell reference to refer
    > > > to the constant, and many relative relative references in rows of formulae.
    > > > I want to copy this range of work and paste it several rows below the
    > > > original. However, I want to use a different constant for the second range
    > > > of formulae. Since I used an absolute reference in my original work, the
    > > > pasted formulae still refer to my original constant. How can I make the
    > > > reference to the original constant be absolute, yet relative to each range of
    > > > formulae so I can change the constant each time I paste the range of
    > > > formulae? For example:
    > > >
    > > > A1=7
    > > > B2=100, C2=B2+A$1
    > > > B3=105, C3=B3+A$1
    > > > B4=110, C4=B4+A$1
    > > >
    > > > Now, copy this range A1:C4 and paste down at A15, now we have:
    > > >
    > > > A15=7
    > > > B16=100, C16=B16+A$1
    > > > B17=105, C17=B17+A$1
    > > > B18=110, C18=B18+A$1
    > > >
    > > > However, what I really wanted was for A$15 to be my new constant (so I could
    > > > change the number in that cell) for this new range of formulae. My actual
    > > > formulae are much more numerous and complicated that this and I am trying not
    > > > to have to go in and manually change each cell reference for the constant on
    > > > my new range. It seems like I need some kind of formula in my original
    > > > constant cell reference (A$1) that will allow it to change when it is pasted
    > > > elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    > > > make them work. Many thanks in advance for your thoughts.


  6. #6
    pfrank
    Guest

    RE: A "relative" absolute cell reference?

    Replace is a good idea and one that will certainly work. However, it just
    seems like there "ought to be a way", right? Sometimes it is best to just
    move on instead of continuing to beat your head against a wall! Thanks for
    your help.


    "JLatham" wrote:

    > The next best thing I can think of is to use Replace. Copy a group from one
    > location on the worksheet to the new location. Note where the constant value
    > (A1 = 7) was in the old group and where it now appears in the new group.
    >
    > With the entire group still selected use Edit | Replace to change (A$1) to
    > the new location, as (A$12) and choose Replace All. Since that's the only
    > place in any of the cells where I see a $ symbol, then it should give you no
    > problems at all if everything else in the group of cells copied and pasted is
    > as you've described here.
    >
    >
    > "pfrank" wrote:
    >
    > > While I think you are heading in the right direction, I would still need to
    > > change the ROW A$1 reference and copy down in my new group. However, my
    > > actual worksheet is quite involved (about 300 rows) and I cannot copy down
    > > the entire column since there are many rows of formulae that I don't want
    > > written over. But, like I said, I think you are on the right track here, and
    > > I am experimenting with OFFSET, ROW and COLUMN. Thanks!
    > >
    > >
    > >
    > > "JLatham" wrote:
    > >
    > > > Try this for the formula in C2:
    > > > =B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))
    > > >
    > > > extend that down through the first group. Now when you copy the entire
    > > > group and move it to another location, just change the ROW(A$1) at the top
    > > > C-column cell in the new group as required and again just drag the formula
    > > > down to the bottom of the new group. That was quick and dirty solution I
    > > > came up with real fast. May even be a better solution, and if so, someone
    > > > else will probably post it shortly.
    > > >
    > > > "pfrank" wrote:
    > > >
    > > > > I have a several rows of formulae that refer to a constant at the upper left
    > > > > corner of the range of formulae. I use an absolute cell reference to refer
    > > > > to the constant, and many relative relative references in rows of formulae.
    > > > > I want to copy this range of work and paste it several rows below the
    > > > > original. However, I want to use a different constant for the second range
    > > > > of formulae. Since I used an absolute reference in my original work, the
    > > > > pasted formulae still refer to my original constant. How can I make the
    > > > > reference to the original constant be absolute, yet relative to each range of
    > > > > formulae so I can change the constant each time I paste the range of
    > > > > formulae? For example:
    > > > >
    > > > > A1=7
    > > > > B2=100, C2=B2+A$1
    > > > > B3=105, C3=B3+A$1
    > > > > B4=110, C4=B4+A$1
    > > > >
    > > > > Now, copy this range A1:C4 and paste down at A15, now we have:
    > > > >
    > > > > A15=7
    > > > > B16=100, C16=B16+A$1
    > > > > B17=105, C17=B17+A$1
    > > > > B18=110, C18=B18+A$1
    > > > >
    > > > > However, what I really wanted was for A$15 to be my new constant (so I could
    > > > > change the number in that cell) for this new range of formulae. My actual
    > > > > formulae are much more numerous and complicated that this and I am trying not
    > > > > to have to go in and manually change each cell reference for the constant on
    > > > > my new range. It seems like I need some kind of formula in my original
    > > > > constant cell reference (A$1) that will allow it to change when it is pasted
    > > > > elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    > > > > make them work. Many thanks in advance for your thoughts.


  7. #7
    JLatham
    Guest

    RE: A "relative" absolute cell reference?

    You'll get no argument out of me on that one. Sometimes the time and effort
    involved in coming up with the "ultimate" solution is never recouped and so
    just using a process that does work ends up being the most economical one.

    "pfrank" wrote:

    > Replace is a good idea and one that will certainly work. However, it just
    > seems like there "ought to be a way", right? Sometimes it is best to just
    > move on instead of continuing to beat your head against a wall! Thanks for
    > your help.
    >
    >
    > "JLatham" wrote:
    >
    > > The next best thing I can think of is to use Replace. Copy a group from one
    > > location on the worksheet to the new location. Note where the constant value
    > > (A1 = 7) was in the old group and where it now appears in the new group.
    > >
    > > With the entire group still selected use Edit | Replace to change (A$1) to
    > > the new location, as (A$12) and choose Replace All. Since that's the only
    > > place in any of the cells where I see a $ symbol, then it should give you no
    > > problems at all if everything else in the group of cells copied and pasted is
    > > as you've described here.
    > >
    > >
    > > "pfrank" wrote:
    > >
    > > > While I think you are heading in the right direction, I would still need to
    > > > change the ROW A$1 reference and copy down in my new group. However, my
    > > > actual worksheet is quite involved (about 300 rows) and I cannot copy down
    > > > the entire column since there are many rows of formulae that I don't want
    > > > written over. But, like I said, I think you are on the right track here, and
    > > > I am experimenting with OFFSET, ROW and COLUMN. Thanks!
    > > >
    > > >
    > > >
    > > > "JLatham" wrote:
    > > >
    > > > > Try this for the formula in C2:
    > > > > =B2+OFFSET(B2,ROW(A$1)-ROW(B2),COLUMN(A1)-COLUMN(B2))
    > > > >
    > > > > extend that down through the first group. Now when you copy the entire
    > > > > group and move it to another location, just change the ROW(A$1) at the top
    > > > > C-column cell in the new group as required and again just drag the formula
    > > > > down to the bottom of the new group. That was quick and dirty solution I
    > > > > came up with real fast. May even be a better solution, and if so, someone
    > > > > else will probably post it shortly.
    > > > >
    > > > > "pfrank" wrote:
    > > > >
    > > > > > I have a several rows of formulae that refer to a constant at the upper left
    > > > > > corner of the range of formulae. I use an absolute cell reference to refer
    > > > > > to the constant, and many relative relative references in rows of formulae.
    > > > > > I want to copy this range of work and paste it several rows below the
    > > > > > original. However, I want to use a different constant for the second range
    > > > > > of formulae. Since I used an absolute reference in my original work, the
    > > > > > pasted formulae still refer to my original constant. How can I make the
    > > > > > reference to the original constant be absolute, yet relative to each range of
    > > > > > formulae so I can change the constant each time I paste the range of
    > > > > > formulae? For example:
    > > > > >
    > > > > > A1=7
    > > > > > B2=100, C2=B2+A$1
    > > > > > B3=105, C3=B3+A$1
    > > > > > B4=110, C4=B4+A$1
    > > > > >
    > > > > > Now, copy this range A1:C4 and paste down at A15, now we have:
    > > > > >
    > > > > > A15=7
    > > > > > B16=100, C16=B16+A$1
    > > > > > B17=105, C17=B17+A$1
    > > > > > B18=110, C18=B18+A$1
    > > > > >
    > > > > > However, what I really wanted was for A$15 to be my new constant (so I could
    > > > > > change the number in that cell) for this new range of formulae. My actual
    > > > > > formulae are much more numerous and complicated that this and I am trying not
    > > > > > to have to go in and manually change each cell reference for the constant on
    > > > > > my new range. It seems like I need some kind of formula in my original
    > > > > > constant cell reference (A$1) that will allow it to change when it is pasted
    > > > > > elsewhere. I have tried INDIRECT and OFFSET, but can't come up with a way to
    > > > > > make them work. Many thanks in advance for your thoughts.


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1