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
Start by looking at Vlookup function in Excel Help.. should do what you want...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
That's really confused me!!
I should mention I'm relatively inexperienced with Excel
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....
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.
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.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
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...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks