Get rid of #VALUE error message

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Get rid of #VALUE error message

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Location
    Northumberland, UK
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Get rid of #VALUE error message

     
    I have a simple formula =IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),F42*0.8) which I am using to create a price list.

    However, there are blank rows which creates the error #VALUE. What do I need to add into this formula to prevent the error message appearing?

    I am using Excel 2010 on Win 7

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    80,967
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get rid of #VALUE error message

    Try...

    =IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),N(F42)*0.8)
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Location
    Northumberland, UK
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get rid of #VALUE error message

    Hi Aladin, that gets rid of the value error but it does drop a zero in. Can it be worked to show a blank cell? I should have been more specific in my original question

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    80,967
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get rid of #VALUE error message

    Quote Originally Posted by SimonHughes View Post
    Hi Aladin, that gets rid of the value error but it does drop a zero in. Can it be worked to show a blank cell? I should have been more specific in my original question
    You could custom format the formula cell as:

    [=0]"";General

    Otherwise:

    Either...

    =IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),IFERROR(F42*0.8,""))

    Or...

    =IFERROR(VLOOKUP(A42,'Sales Pricing.rdl'!$B$5:$G$1936,6,0),IF(ISNUMBER(F42),F42*0.8,""))
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular
    Join Date
    Sep 2009
    Location
    Northumberland, UK
    Posts
    354
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get rid of #VALUE error message

    That works perfectly, many thanks

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    80,967
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Get rid of #VALUE error message

      
    Quote Originally Posted by SimonHughes View Post
    That works perfectly, many thanks
    You are welcome. Thanks for providing feedback.
    Assuming too much and qualifying too much are two faces of the same problem.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com