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

  • Thread starter Thread starter ojos_delangel
  • Start date Start date
O

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
 
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
 
Use two cells...

Cell1:

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

Cell2:

=IF(ISNUMBER(Cell1),Cell1*$I$10,"")
 
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
 
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!

:
 
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
 
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.
..
 
Mike! You are a genius!!:cool: 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 :D

Nat
 
Back
Top