Bit of a Tricky One
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Bit of a Tricky One

  1. #11
    New Member
    Join Date
    Nov 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bit of a Tricky One

    That's not a bad idea, but in this instance I wouldn't want to see an error returned to let me know that something went wrong. If it returns a blank, that tells me nothing has been inputted in the cell that the formula is drawing from. I'm using these as order entry sheets so instead of a bunch of N/A's we want to see just empty cells. I do have a more complicated one if you're still available though?

  2. #12
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,067
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Bit of a Tricky One

    but in this instance I wouldn't want to see an error returned to let me know that something went wrong. If it returns a blank, that tells me nothing has been inputted in the cell that the formula is drawing from. I'm using these as order entry sheets so instead of a bunch of N/A's we want to see just empty cells.
    That is precisely what the last formula I posted would do.
    Your original formula might return N/As, if your conditions is met, but it doesn't find anything when looking up the value.

    I do have a more complicated one if you're still available though?
    If it is a new question that is not dependent upon knowing the answer and discussion around this one, it is best to post it to a new question. That way others will see it as a new unanswered question, in case I am not available or able to help. And there are people on this board smarter than me!
    Last edited by Joe4; Jun 25th, 2019 at 10:52 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #13
    New Member
    Join Date
    Nov 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bit of a Tricky One

    Interesting, I was getting back zero's (0) is some cells when the formula was working correctly, but for the life of me couldn't figure out why. Let me input that and see if that clears it up.

    My next one is somewhat tied to this one, but would now include four cells (A2-D2) and include headers (A1-D1). Basically, I need the formula to return the header value, but only from the cell below it that contains data. So if B2 contained a value then it would return the value in B1.

  4. #14
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,067
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Bit of a Tricky One

    My next one is somewhat tied to this one, but would now include four cells (A2-D2) and include headers (A1-D1). Basically, I need the formula to return the header value, but only from the cell below it that contains data. So if B2 contained a value then it would return the value in B1.
    While having some similiarities, it sounds like it is an entirely different question that would have a very different solution, probably an INDEX/MATCH solution. So it is probably best to post that to a new thread. I have done some INDEX/MATCH solutions, but it is not "in my wheelhouse", and there are others on this board who are far more proficient at it than me.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #15
    New Member
    Join Date
    Nov 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bit of a Tricky One

    No worries, you've been a lot of help! I tried that IFERROR solution, it's working but it not's working on a similar formula. Any suggestions?

    =IF(H3=8,(VLOOKUP(B3,'B:\Menu Database\[BeerDatabase1.xlsx]Sheet1'!$A$3:$AG$40000,33,FALSE)),"")

    I put the IFERROR after the H3=8,
    Last edited by boojooo; Jun 25th, 2019 at 11:04 AM.

  6. #16
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,067
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Bit of a Tricky One

    The format you want to follow is this:
    IFERROR(VLOOKUP(...),"")
    Basically, the way that it works is it has two arguments.
    First argument: the formula you are checking
    Second argument: what to return if the formula in the first argument returns an error

    So, it will either return the value returned by the formula in the first argument, or what you tell it to return in case of an error.

    So, to update your other formula, you would make it look like:
    Code:
    =IF(H3=8,IFERROR(VLOOKUP(B3,'B:\Menu Database\[BeerDatabase1.xlsx]Sheet1'!$A$3:$AG$40000,33,FALSE),""),"")
    What may make it a little confusing is that your original formula has an extra set of parentheses which are not necessary. Your original formula could have been written just like this:
    Code:
    =IF(H3=8,VLOOKUP(B3,'B:\Menu Database\[BeerDatabase1.xlsx]Sheet1'!$A$3:$AG$40000,33,FALSE),"")
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #17
    New Member
    Join Date
    Nov 2012
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Bit of a Tricky One

    Lol, you're right on the second one. I got a little do () happy.

    The first equation worked perfectly! Man that was driving me nuts when the zero's kept appearing randomly. Thank you again for all your help!

  8. #18
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,067
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Bit of a Tricky One

    You are most welcome!
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

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
  •