Vlookup & if Statement, PLEASE HELP!!(kevin or george??)

[ojos_delangel]

Hi,

First I would like to thank those who helped "Brian" with his N/A value
error by creating the blank cell. that finally helped me out with 1 out
of 2 problems (that one I'd say being the most difficult!)

Now, the problem that I am having is that by using the blank cell, it
is creating a "value" error in a cell across from it as that cell is
part of a Sum in another cell to get a total cost, so with it creating
the blank cell it is causing a invalid value for the sum cell

this is my formula
=IF(ISNA(VLOOKUP(A38,$A$134:$B$139,2,FALSE)*$I$10),"",VLOOKUP(A38,$A$134:$B$139,2,FALSE)*$I$10)

there is another drop down adjacent to this and then finally there is a
sum cell at the end, so what I would like is if there is a blank field
chosen in either drop down then it produces a 0 value and if both are
blank then it itself produces a blank field.

Gosh, I hope someone understood this, frankly, I think I just may have
confused myself.

I am supposed to have this form finished by tomorrow (Friday), so
Please if anyone can help!!

thank you again!
Take care,
Nat

 

[Kevin Stecyk]

Nat,

I was unable to understand your post, but perhaps it will make sense to
others.

What you might want to try doing is this:

1) The quick objective of what you are trying to accomplish with your
spreadsheet
2) A quick overview of the layout ( we see that there are lookup tables and
such)
3) Key formulas that are causing you grief. Where are they and what is the
specific issue?

I note your formula below. The only change I could suggest now is as
follows.(it's minor)...

=IF(ISNA(VLOOKUP(A38,$A$134:$B$139,2,FALSE)),"",VLOOKUP(A38,$A$134:$B$139,2,
FALSE)*$I$10)

I think you want to test if vlookup returns a #NA error?

I did test to see if sum worked across a blank cell ("") and sums do work
across blank cells. So that is where I lost you. You mention value error,
yet I am unable to understand it or reproduce it. So for someone to assist,
I think they will need more information.

4) What is it precisely that you are looking to accomplish? I note your
"value" error and I note your wanting dropdowns to provide different fields
and values. If you have two objectives, please state them clearly so we
understand what is being asked.

Wrapping it up, I found your question difficult to understand. But if
someone sees the path more clearly, then by all means please jump in and
assist Nat without waiting for further explanation.

Sorry, I can't provide any guidance, but I am unsure of what is being asked.

Nat, once you have articulated your question more clearly, I am sure someone
will assist you.

Regards,
Kevin

 

[Aladin Akyurek]

Use two cells...

Cell1:

=VLOOKUP(A38,$A$134:$B$139,2,0)

Cell2:

=IF(ISNUMBER(Cell1),Cell1*$I$10,"")

 

[Freemini]

If you replace the double quotes ("") in your formula with a zero thi
will get rid of the #Value error but your "blnak2 cell will now contai
0.

By using Format cells>Number>custom and setting the third part of th
format i.e ###0.00;-###0.00;

this will give you
positve numbers with 2decimal places
negative numbers with a minus sign and 2 decimal places
zero values will be shown as blanks (but still have a value)

Hope this helps

Mik

 

[ojos_delangel]

do you mean, that I would have to use 1 cell for if there is only 1 cel
that has a blank AND 1 cell if both are blank? there's no way to put
IF statement's in 1 cell is there?

Thank you for taking the time to help me!

:

 

[Freemini]

I am not sure who you were replying to but my solution to change the "
to zero in the formula assumes that the formula you said you were usin
formed part of another calculation later in the sheet.

I assumed that

=IF(ISNA(VLOOKUP(A38,$A$134:$B$139,2,FALSE)*$I$10)
,"",VLOOKUP(A38,$A$134:$B$
139,2,FALSE)*$I$10)

was the formula you used
I suggested you change it to

=IF(ISNA(VLOOKUP(A38,$A$134:$B$139,2,FALSE)*$I$10)
,0,VLOOKUP(A38,$A$134:$B$
139,2,FALSE)*$I$10)

Hope this is what you wanted
Mik

 

[Aladin Akyurek]

One cell for the straight VLOOKUP formula, one cell for the formula. The one
with IF refers to the other and it houses the result you're looking for.
..

 

[ojos_delangel]

Mike! You are a genius!! that worked perfectly! You don't
understand how happy I am! I have FINALLY finished making this order
form idiot proof!

Thank you!!
Have a great weekend

Nat

 

[Freemini]

Don't be fooled NOTHING is completely idoiot proof


Glad it worked for you

Mik