Quantity Blank, Remaining cells in row appear Blank

[ajaminb]

I have a quote form in Excel. Right now, if I have a zero in the Qty column
it shows the zero, the Sku# in the next column, description in next, and so
on.

If I remove the zero and the part number, the remaining cells in the row
show "NA"; I would like them to appear blank until the number 1 or otherwise,
and the Sku# are put in the fields.

This is the formula I'm using now in the description filed:
=IF(B24="","",VLOOKUP(B24,List!$A$1:$C$114,2,FALSE))

 

[muddan madhu]

try this

=IF(B24="","",VLOOKUP(B24,List!$B$1:$C$114,2,FALSE))

 

[franciz]

I am assuming the your database is in col A1 to C114 under sheet named List
in the same workbook and that you want to look up the match value in B24.
Otherwise you need to elaborate more.

Try this formula, it return a blank for no matching data found


IF(ISNA(VLOOKUP(B24,List!$A$1:$C$114,2,FALSE)),"",(VLOOKUP(B24,List!$A$1:$C$114,2,FALSE))

regards,

 

[Dave Peterson]

=if(b24="","",if(isna(vlookup(...)),"",if(vlookup(...)="","",vlookup(...)))

or

=if(b24="","",if(iserror(1/len(vlookup(...)),"",vlookup(...)))

I like the top one so I can return meaningful info:

=if(b24="","",if(isna(vlookup(...)),"Missing in table",
if(vlookup(...)="","",vlookup(...)))

 

[ajaminb]

Excuse my poor description. I'll try again.

In Excel I have a product quote form; there is the front sheet that is the
form and a second sheet "List" that is the source of data.

On the front Quote sheet the row is as follows: A1 is Quantity / B1 is SKU#/
C1 is Product Description / D1 is Unit Price / E1 is Total Amount.

On the second sheet: A1 is SKU# / B1 is Product Description / C1 is Unit Price

On the front sheet the formula in C1 is:
=IF(B1="","",VLOOKUP(B1,List!$A$1:$C$114,2,FALSE)) In the next cell D1 is:
=IF(B24="","",VLOOKUP(B24,List!$A$1:$C$121,3,FALSE)) and the Amount cell E1
is: =A1*C1

With all of the formulas in place on the front sheet, when I enter a Sku#
the data in the remaining cells in the row are populated, except for the
amount. When I enter the quantity, the amount appears that is multiplied by
the quantity.

I would like all of the fields to be blank until I enter the Sku# and
Quantity; when I take away the Sku# I get #N/A in C1, D1 and E1. I would
like them to be blank.

Solution?

Do I need to apply "Conditional Formatting" to cells C1, D1 and E1?

 

[franciz]

I am not sure what you want to accomplish based on your description

The formula that I have provided above does what you want.
I will reproduce it here.
I gather that you put your formulas in 2nd row and not the first since the
first row
is your fileds. You need to change the cell references to yours.I will
ignore B24 since you didn't provide what that cell for.

Put these and drag down as far as you want
In C2,
IF(ISNA(VLOOKUP(B2,List!$A$2:$C$7,2,0)),"",VLOOKUP(B2,List!$A$2:$C$7,2,0))
In D2

=IF(ISNA(VLOOKUP(B2,List!$A$2:$C$7,3,0)),"",VLOOKUP(B2,List!$A$2:$C$7,3,0))

The Vlookup will populated the related data from your source in col C and
col D
and 0 in col E with your existing formula in col E if there is no qty

Does this do what you want?

regards,

 

[ajaminb]

Thank you for spelling this out so well. Yes, this is working, however, in
the last cell "Amount" (E2) when I remove the Quanity or Sku# cell E2
"Amount" displays "#VALUE!" in the cell; the formula I have in E2 is:
=A2*D2. I'm thinking there is something I can do to have nothing displayed.
Do you have any ideas?

 

[ShaneDevenshire]

Hi,

I'm going to take a shot at this one - how are you clearing the cells B1 or
B24? Are you pressing Spacebar enter, or are you pressing Del?

A spacebar does not clear the cell.

 

[ajaminb]

The cells are actually cleared as a result of deleting the Sku# in B1.