+ Reply to Thread
Results 1 to 8 of 8

Absolute cell references...changing?

  1. #1
    Registered User
    Join Date
    03-09-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Absolute cell references...changing?

    Hi, just a quick question really, I have a feeling it can probably be resolved quite quickly but I just don't know how.

    Basically, I have a formula containing absolute cell references, but whenever I insert a new row into the table that the reference is looking at, the cells bound by the absolute reference change.

    I thought the point of using absolute cell references is that they don't change?

    Anyway, is there some way around this, to avoid the cell reference changing when a row is inserted?

    Thanks in advance for any help.
    Last edited by Dharrison8; 03-10-2010 at 04:46 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Absolute cell references...changing?

    The only way is to indirectly refer to the cells.. via an Indirect() function or Index() function...

    We would need to know more specifically what the cell ranges and formulas are...

    Perhaps upload a sample workbook.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-09-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Absolute cell references...changing?

    An example workbook may be difficult to do, as I want all of this to then be running within a macro too.

    But essentially, I need:

    =VLOOKUP(B11, 'Item & Stock'!$A$12:$J$29, 3)

    To remain as it is, even when inserting a row into the "Item & Stock" sheet (the inserting of a row is to add a new item into the "Item & Stock" sheet)

    Instead, it changes to:

    =VLOOKUP(B11, 'Item & Stock'!$A$13:$J$30, 3)

    With 'Item & Stock'!$A$12:$J$29' simply being the Item's names, prices etc.


    I'm just confused, I've never come across the functions you've put forward either

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,675

    Re: Absolute cell references...changing?

    One solution for this specific situation that doesn't require changing your formula is to make the first row of cells in your lookup range blank, let's say that's A12:J12. Then if you need to insert a new row at the top of the lookup range, insert it below the blank line. That will automatically cause the range in your formula to grow by one line to include the new data, but still start in the correct place.

    I thought the point of using absolute cell references is that they don't change?
    Not exactly. It means it's an absolute reference, compared to a relative reference. It means that if you copy that reference to another place, it will still refer to the same location (that is, a reference to $D$24 will refer to $D$24 no matter where you copy it to). With a relative reference, if you copy it someplace else, it will refer to a different location that is in the same position relative to the referencing cell (that is, if you have a formula in A1 that refers to B2, then copy it to F6, the formula will be updated to refer to G7). An absolute reference will be updated by Excel if you add or delete rows or columns to ensure that it still refers to the same data, even though the address changes.
    Last edited by 6StringJazzer; 03-10-2010 at 03:04 PM.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Registered User
    Join Date
    03-09-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Absolute cell references...changing?

    But I will not just be adding the one item.

    Wouldn't that only work if one row was added, because after adding a second row, the cell reference will be looking at 1 row below where I want it to.

    E.g. If I leave row 12 blank, then add another row, the formula will work, as it will look up row 13. But then when I add a further row, which then becomes row 13 itself, the formula will change to look at row 14, so missing out row 13 entirely?

    I hope that made sense :s

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Absolute cell references...changing?

    Does this work?

    =VLOOKUP(B12, INDEX('Item & Stock'!$A:$A,12):INDEX($J:$J,29), 3)

  7. #7
    Registered User
    Join Date
    03-09-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Absolute cell references...changing?

    Just trying out both ideas, bear with me

    Also, thanks for all the help!

  8. #8
    Registered User
    Join Date
    03-09-2010
    Location
    Warrington, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Absolute cell references...changing?

    I've just tried "6StringJazzer's" approach, and it seems to have worked out okay, so for now I'll go ahead with that one

    Thanks again to both of you though, I really appreciate your help.

+ 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