+ Reply to Thread
Results 1 to 14 of 14

offset function ref error - how to stop the error showing

  1. #1
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Question offset function ref error - how to stop the error showing

    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
    ---------------------
    Attached Files Attached Files
    Last edited by Hattie_Dog; 04-15-2021 at 06:57 AM.
    Hattie

    Windows 10 pro 64 Bit
    Office 365 64 Bit

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Re: offset function

    original post amended but i think i have just said the same thing just differently, hopefully it makes more sense

    i just don't want the #ref! to appear when a row is deleted and replaced with a new row

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function ref error - how to stop the error showing

    Thanks for updating the title. I'll have a look.

    solve my issue.
    I think you forgot your manners - how about 'please'? Don't take us for granted, and remember all the help you get comes for free and on someone else's free time.
    Last edited by AliGW; 04-15-2021 at 07:03 AM.

  5. #5
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Re: offset function ref error - how to stop the error showing

    I know and i appreciate the help and i did say thank you at the end of my message

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function ref error - how to stop the error showing

    See if you can work with the arrangement in the attached, where I have created a structured table and used this:

    =OFFSET(Table1[[#Headers],[Column3]],1,0

    This continues to work if you remove rows.

    I know and i appreciate the help and i did say thank you at the end of my message
    Then don't issue commands. Request assistance, don't demand it.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Re: offset function ref error - how to stop the error showing

    thanks I will try that in my sheet.

    Sorry,

    I was not demanding help, I was asking, i don't understand where i was!

    which part of my text is not polite?

    apologies for any offence caused - it was unintentional

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function ref error - how to stop the error showing

    This bit:

    see attached sheet.
    solve my issue.
    Let us know how you get on with the suggestion.

  9. #9
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Re: offset function ref error - how to stop the error showing

    that solution works perfectly thanks

    i will go and read up about how it works.

    thanks again

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function ref error - how to stop the error showing

    You're welcome.

  11. #11
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Re: offset function ref error - how to stop the error showing

    "see attached sheet.
    solve my issue."

    I see.

    I didn't add the "solve my issue" text that was not typed by me - I don't know how it got there sorry.

    could it be the default text on the attach file page?

    i certainly didn't type it or even see it until you have pointed it out to me.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function ref error - how to stop the error showing

    Well, you did type it - it could not have got there otherwise. The forum does not automatically enter anything into your posts, nor has your post been edited by anybody else.

    Anyway, I have made my point.

  13. #13
    Registered User
    Join Date
    02-13-2021
    Location
    UK
    MS-Off Ver
    365
    Posts
    47

    Re: offset function ref error - how to stop the error showing

    sorry,

    I can't find it anywhere in my spreadsheet or on the manage upload page.

    it's not in the original text file i composed the message in.

    confused.

    sorry anyway it was unintentional

    i will double check next time before i post

  14. #14
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,327

    Re: offset function ref error - how to stop the error showing

    Proofreading is the way to go.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 08-14-2017, 06:26 AM
  2. Replies: 1
    Last Post: 06-10-2015, 12:56 PM
  3. Offset function with reference cell equal to the value of match function
    By cmurda in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-08-2014, 02:09 PM
  4. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  5. [SOLVED] Using Offset function as the array in the PercentRank function is giving wrong result
    By Bobneil in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-06-2013, 09:29 PM
  6. Replies: 0
    Last Post: 01-19-2013, 01:35 PM
  7. function similar to offset to offset the offset
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2012, 03:07 PM

Tags for this Thread

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