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 03:46 PM.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
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.
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.I thought the point of using absolute cell references is that they don't change?
Last edited by 6StringJazzer; 03-10-2010 at 02:04 PM.
Making the world a better place one fret at a time
||||||
If someone helped you, please click on the star icon at the bottom of their post
If your problem is solved, please update the first post:
EDIT, Go Advanced button, set Prefix to SOLVED
[code]
' Enclose code in tags like this
[/code]
Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.
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
Does this work?
=VLOOKUP(B12, INDEX('Item & Stock'!$A:$A,12):INDEX($J:$J,29), 3)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Just trying out both ideas, bear with me
Also, thanks for all the help!
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks