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 ))
--
AjaminB

try this

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


On Sep 27, 7:09*pm, ajaminb wrote:
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 ))
--
AjaminB

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,

"ajaminb" wrote:

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 ))
--
AjaminB

=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 wrote:

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 ))
--
AjaminB

--

Dave Peterson

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?

--
AjaminB


"franciz" wrote:

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,

"ajaminb" wrote:

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 ))
--
AjaminB

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)),"",VLOOKU P(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" wrote:

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?

--
AjaminB


"franciz" wrote:

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,

"ajaminb" wrote:

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 ))
--
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?
--
AjaminB


"franciz" wrote:

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)),"",VLOOKU P(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" wrote:

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?

--
AjaminB


"franciz" wrote:

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,

"ajaminb" wrote:

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 ))
--
AjaminB

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.
--
Thanks,
Shane Devenshire


"ajaminb" wrote:

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 ))
--
AjaminB

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


"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"ajaminb" wrote:

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 ))
--
AjaminB