Microsoft Excel - How do i do this?

[FrankCastle]

Hi guys

When I was working at my previous company we had pro forma invoices templates that had all the product info already embedded within the workbook.

All I had to do was put the product code in the first column and the description as well as the price would automatically pop up in the appropriate columns.

I would then manually add the quantity and the total amount per product would automatically be displayed.

When completed I just deleted the remaining cell and the full total of the invoice would be given.

How do I create something like this and what is this process called so I can do some research as well.

Any assistance would be greatly appreciated.

 

[krono9]

I would probably have a sheet..possibly hidden thats basically a price list.... items and prices... and on the invoice I would use the vlookup function to automatically match a code entered on the invoice to the price list which would then populate the invoice.. the rest are just standard formulas....

 

[PallBearer]

vlookup

 

[CheekyC]

Or odbc link to a db

 

[DJ...]

Vlookup for sure. It's the simplest method...

 

[DrJohnZoidberg]

As the others have stated you can add a sheet in the workbook with three columns, e.g) code, description, price

For example we have a sheet called pricelist, it looks like this:

Then we have the invoice sheet, with the following formulae:

This is very simplified, but you get the idea.


Another solution would be to connect to an external database, but I don't think we'll be going there right now

 

[maumau]

Is there a reason for IFERROR, why not just =vlookup?

 

[DrJohnZoidberg]

Is there a reason for IFERROR, why not just =vlookup?

It just makes things look neater, if you don't have a value the formula can work with it shows a #NA error in the cell - with IFERROR you can tell it what to put in the cell if there is an error, in this case a blank space.

 

[maumau]

Oh cool, tks DrJohn. Very nice, those N/As look awful.

 

[FrankCastle]

I would probably have a sheet..possibly hidden thats basically a price list.... items and prices... and on the invoice I would use the vlookup function to automatically match a code entered on the invoice to the price list which would then populate the invoice.. the rest are just standard formulas....

vlookup

Vlookup for sure. It's the simplest method...

As the others have stated you can add a sheet in the workbook with three columns, e.g) code, description, price

For example we have a sheet called pricelist, it looks like this:

Then we have the invoice sheet, with the following formulae:

This is very simplified, but you get the idea.


Another solution would be to connect to an external database, but I don't think we'll be going there right now

Is there a reason for IFERROR, why not just =vlookup?

Thanks to all the responses.

Used vlookup and its up and running but there's a small snag perhaps someone can help.

In my product code column certain cells do not have a small green triangle in the top left, and these are the cells that don't transfer or don't seem to be linked linked to my invoice.

What could be the problem here?