Using Excel for quotations and looking up descriptions and other information

[Neil.Goldsborough]

I'm wanting to use excel to prepare quotations. Nothing complicated, but I have approx 500 products in total which I will enter into an excel spreadsheet, but I only want to do it once.
After that, I would like to only have to enter a product code and have excel 'bring in' all the rest of the details (description / specification / price etc).

Is this possible and if so, where do I start and how do I set it up?

Many thanks,


Neil

[NBVC]

Start by looking at Vlookup function in Excel Help.. should do what you want...

[Neil.Goldsborough]

That's really confused me!!

I should mention I'm relatively inexperienced with Excel

[NBVC]

So if say you entered all your raw data in Sheet1 with product code in column A, descriptions in column B, and other relevant info in adjacent columns....

Now you enter a product code in Cell A1 of Sheet2, to get the description from Sheet1 for that code, use this formula in the cell you want to get the result in:

=Vlookup(A1,Sheet1!$A$1:$D$100,2,FALSE)

This will look for value you entered in A1 and find it in Sheet1, A1:A100 and then extract the info from the 2nd column in the table A1:D100, i.e. column B.

To get next column of data, use same formula and change the 2 to a 3, etc...

Make sure you encapsulate the full table in the second argument of the formula.

If you still have difficulties...post a sample workbook removing confidential data....

[Neil.Goldsborough]

OK. I'm still struggling. I've attached the workbook as I have it. I want to be able to just enter a quantity and model number in the quotation page and let excel input the reset of the data.

[NBVC]

See attached...


Note.. I put in sum fictitious prices to show how it works...

I also made assumptions about your discount, nett and totals ... change as you see fit.

[Neil.Goldsborough]

Nearly perfect. The only problem I have now is that each of the fields into which I have copied the formula displays #N/A until a model no. is entered for that row. I know is other excel commands I can use the =IF(SUM(B16)>0, (example) prefix to leave the field blank, but if I use it in this case, it displays 'FALSE'

Can this be resolved?

Thanks,

Neil

[NBVC]

You can add a condition to check if you have entered a model #...

=IF(C15="","",VLOOKUP($C15,'Price List'!$A:$D,4,FALSE))

See attached...