+ Reply to Thread
Results 1 to 7 of 7

Keeping cell reference the same after inserting rows

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Keeping cell reference the same after inserting rows

    I have a worksheet which copies a group of cells below the last data line in row A by using a macro and a button. When the button is hit the range A30:DQ54 is copied and pasted below the last row of data in row A. This works well.

    The problem is I need the formulas in columns I to P (I30:P54)to reference the Yellow Row even when inserting rows in the section just copied. This can be seen in the range A80:DQ104 on the attached sheet, in this range I am able to make the formula with absolute numbers so when inserting rows the formulas still reference the yellow row (Row 80). The problem formulas are only in the columns I-P.

    My question: is there a way to write the formula in cells I30:P54 so that when they are copied with the button, the formulas will always reference the yellow line even when inserting new rows between the top row (yellow) and the bottom row (green) of the range? (After copying)

    I've attached the page so you can see the problem better.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    04-13-2012
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Keeping cell reference the same after inserting rows

    Try replacing I30 with INDIRECT("I30")

    I.e. instead of:
    =IF($G31="","-",($G31*I30))

    try:
    =IF($G31="","-",($G31*INDIRECT("I30")))

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Re: Keeping cell reference the same after inserting rows

    Thank you for the reply Gazp, the formula does work within the range but does not transfer when the macro is run and the range is added to the end of data. It still references the I30 line instead of the new yellow line which would be I105. That's the whole problem is getting it to copy correctly and then being able to insert lines without screwing up the formulas. I had the same problem when using INDEX to reference the yellow cell.

    I do like the INDIRECT you suggested, I'm going to use it in other sheets in the workbook.


    This is a really annoying problem.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Keeping cell reference the same after inserting rows

    =if($g31="","-",($g31*index($i:$i,30)) is probably better than indirect,however that dosent answer your question.
    Last edited by martindwilson; 08-25-2012 at 08:11 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Re: Keeping cell reference the same after inserting rows

    Thanks Martin. Yes that does eliminate the insert row issue, still trying to figure out the copy problem. Maybe some type of index/match as the numbers are keyed off of a different sheet.

    I appreciate the reply.

  6. #6
    Registered User
    Join Date
    08-10-2012
    Location
    Orlando
    MS-Off Ver
    O365
    Posts
    24

    Re: Keeping cell reference the same after inserting rows

    Bumping to the top. Anyone have the answer??

  7. #7
    Registered User
    Join Date
    04-13-2012
    Location
    Leeds, UK
    MS-Off Ver
    Excel 2007
    Posts
    46

    Re: Keeping cell reference the same after inserting rows

    Almost, expanding on Martin's tip, try something like:

    =if($g31="","-",($g31*index($i:$i,ROW(I31)-1)))

    You would need to manually increment the '-1' manually to '-2' '-3' etc. in subsequent rows, unless someone can give a more elegant solution?

+ 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