Hi,
I don't want the offset function to display #ref! when I delete the row it is looking at.
when i delete the row it is looking at (row6) a new row from underneath pops up to take its place (so row 7 will become the new row 6) so the row is still there really.
but the offset function will display the ref! error
there is always going to be a row 6, just a new line 6 when you delete the original line 6.
so if you delete the row 6 with 'mickey' on it the new row 6 will have 'mouse' on it
I just want the text to change from 'mickey' to 'mouse' not to #ref!
I am using the offset function, which works to a point.
But it doesn't have to be the offset function if there is a better way to do it.
see attached sheet.
solve my issue.
If I delete Line A (mickey) the cell with the offset formula in it no longer shows 'mickey' it shows #ref!
I want it now to show the new that replaces it Line B (mouse) in this case,
the line that has moved up to replaces Line A
Excel see that's line A has been deleted and show that the reference has gone.
OK, I understand this, I can see the point of this.
But...
Can I make it ignore this error in this particular case so I can all ways see the contents of this particular cell?
Or is there an alternative method for doing such a thing.
Thank you
---------------------
copy of attached
OFFSET($E$6,0,0,1,1)
Cell with formula> mickey
Line A mickey
Line B mouse
Line C donald
Line D duck
---------------------
Bookmarks