I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.

John,

One way is to use vlookup with a formula like:

in row2 C2
=if(isna(vlookup(B2, products!a:c,3,false)),
"No Product", vlookup(B2, products!a:c,3,false)

What this does in takes the product code goven in b2 and looks it up in a
products sheet where column a holds the code, column b the product
description and column c the sales price and if it is not there it reports no
product.

There are some problems with this method as if you change the sales price
all the historical prices change too, this may or may not be a problem.

The other way to do it and personally my preferred method is to use a
userform and get the details that way and them update the worksheet with the
actual price using th e number and not a formula.

i leave that for you to descide.


Hope this helps
Martin Fishlock


"John Abetong" wrote:

I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.

Hello Martin,

What happen was #N/A. Probably because the ITEM column is a list table and
not a text? List table of ITEM & UNIT PRICE is in worksheet 2 and main
invoicing data entry in worksheet 1.

Let me know what other things we need to check further to correct the errors.

Thanks.
John

"Martin Fishlock" wrote:

John,

One way is to use vlookup with a formula like:

in row2 C2
=if(isna(vlookup(B2, products!a:c,3,false)),
"No Product", vlookup(B2, products!a:c,3,false)

What this does in takes the product code goven in b2 and looks it up in a
products sheet where column a holds the code, column b the product
description and column c the sales price and if it is not there it reports no
product.

There are some problems with this method as if you change the sales price
all the historical prices change too, this may or may not be a problem.

The other way to do it and personally my preferred method is to use a
userform and get the details that way and them update the worksheet with the
actual price using th e number and not a formula.

i leave that for you to descide.


Hope this helps
Martin Fishlock


"John Abetong" wrote:

I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.

John,

The main reason for the n/a is trext and numbers. It is a real pita
therefore convert the lookup key to the same type as the lookup table and it
should work.

--
Hope this helps
Martin Fishlock


"John Abetong" wrote:

Hello Martin,

What happen was #N/A. Probably because the ITEM column is a list table and
not a text? List table of ITEM & UNIT PRICE is in worksheet 2 and main
invoicing data entry in worksheet 1.

Let me know what other things we need to check further to correct the errors.

Thanks.
John

"Martin Fishlock" wrote:

John,

One way is to use vlookup with a formula like:

in row2 C2
=if(isna(vlookup(B2, products!a:c,3,false)),
"No Product", vlookup(B2, products!a:c,3,false)

What this does in takes the product code goven in b2 and looks it up in a
products sheet where column a holds the code, column b the product
description and column c the sales price and if it is not there it reports no
product.

There are some problems with this method as if you change the sales price
all the historical prices change too, this may or may not be a problem.

The other way to do it and personally my preferred method is to use a
userform and get the details that way and them update the worksheet with the
actual price using th e number and not a formula.

i leave that for you to descide.


Hope this helps
Martin Fishlock


"John Abetong" wrote:

I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.

Hi Martin,

Can you give me specific example of converting the lookup key table.

=if(isna(vlookup(B2, products!a:c,3,false)),"No Product", vlookup(B2,
products!a:c,3,false)

"Martin Fishlock" wrote:

John,

The main reason for the n/a is trext and numbers. It is a real pita
therefore convert the lookup key to the same type as the lookup table and it
should work.

--
Hope this helps
Martin Fishlock


"John Abetong" wrote:

Hello Martin,

What happen was #N/A. Probably because the ITEM column is a list table and
not a text? List table of ITEM & UNIT PRICE is in worksheet 2 and main
invoicing data entry in worksheet 1.

Let me know what other things we need to check further to correct the errors.

Thanks.
John

"Martin Fishlock" wrote:

John,

One way is to use vlookup with a formula like:

in row2 C2
=if(isna(vlookup(B2, products!a:c,3,false)),
"No Product", vlookup(B2, products!a:c,3,false)

What this does in takes the product code goven in b2 and looks it up in a
products sheet where column a holds the code, column b the product
description and column c the sales price and if it is not there it reports no
product.

There are some problems with this method as if you change the sales price
all the historical prices change too, this may or may not be a problem.

The other way to do it and personally my preferred method is to use a
userform and get the details that way and them update the worksheet with the
actual price using th e number and not a formula.

i leave that for you to descide.


Hope this helps
Martin Fishlock


"John Abetong" wrote:

I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.

Guys,

I solved the problem. I used data validation for item list. I defined Column
A and B for Item & Price respectively in Worksheet 2. Validate Data in
Worksheet 1 so then user will just tick an item and the price is propagated
immediately.

Thanks for your help and effort. Greatly appreciated.

Thanks.
John

"John Abetong" wrote:

Hi Martin,

Can you give me specific example of converting the lookup key table.

=if(isna(vlookup(B2, products!a:c,3,false)),"No Product", vlookup(B2,
products!a:c,3,false)

"Martin Fishlock" wrote:

John,

The main reason for the n/a is trext and numbers. It is a real pita
therefore convert the lookup key to the same type as the lookup table and it
should work.

--
Hope this helps
Martin Fishlock


"John Abetong" wrote:

Hello Martin,

What happen was #N/A. Probably because the ITEM column is a list table and
not a text? List table of ITEM & UNIT PRICE is in worksheet 2 and main
invoicing data entry in worksheet 1.

Let me know what other things we need to check further to correct the errors.

Thanks.
John

"Martin Fishlock" wrote:

John,

One way is to use vlookup with a formula like:

in row2 C2
=if(isna(vlookup(B2, products!a:c,3,false)),
"No Product", vlookup(B2, products!a:c,3,false)

What this does in takes the product code goven in b2 and looks it up in a
products sheet where column a holds the code, column b the product
description and column c the sales price and if it is not there it reports no
product.

There are some problems with this method as if you change the sales price
all the historical prices change too, this may or may not be a problem.

The other way to do it and personally my preferred method is to use a
userform and get the details that way and them update the worksheet with the
actual price using th e number and not a formula.

i leave that for you to descide.


Hope this helps
Martin Fishlock


"John Abetong" wrote:

I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.

Try this:

IF(ISNA(VLOOKUP("particular item",B2:C100,2,0)),"",VLOOKUP("particular
item",B2:C100,2,0))


"John Abetong" wrote:

I want to create a simple invoicing from a list of table. Like what I have
below:

A B C
D
Date Item (list table) Unit Price (should be automatic) Manually
entered

How to defined a function in Column C if I select a particular item in the
list table, it displayed its corresponding unit price.

Your help is greatly appreciated.

Thanks.