Bit of a Tricky One

[boojooo]

Hello,

I have a bit of a tricky one, maybe. I have formulas in both cells A1 and B1 that will either return a value or be left blank. What I would like to do is create another formula in cell D1 that returns the value of cell C1, but only if either cells A1 or B1 have values in them (not blank). I think I'm close with this formula in cell D1: IF(or(A1>0,B1>0),(C1,"")

 

[boojooo]

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?

 

[Joe4]

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!

 

[boojooo]

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.

 

[Joe4]

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.

 

[boojooo]

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,

 

[Joe4]

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:

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

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:

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

 

[boojooo]

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!

 

[Joe4]

You are most welcome!