Forum Discussion
Pick & Choose numbers from connected Excel File
Hello,
I am not 100% familiar with Excel, I'd say I'm a beginner. So laymen terms is great.
My issue is the company I work for has an excel spreadsheet with all of our products part #s and prices in it (thousands of items). I am looking for a way to connect this spread sheet to a second one. In the second spreadsheet, representatives would enter a part number, and the information from the first sheet would appear (# and price) so they can generate a list for customer orders.
Because our sales reps are not computer literate, we are looking for a simple "type & enter" sort of solution so that they only have to type a part number. We would also like the prices to automatically add up to a total with every additional number added to the list or taken away.
Is this possible?
5 Replies
- Hello, it is possible. I attached an excel file that I used VLOOKUP to solve
There are two sheets: Customer Quite and General Price List
In the Customer Quote sheet, there are Product ID, Qty, Product Description, Price and Total Columns
In the General Price List, we have the same column with many data.
To get the corresponding Product Description in General Price List sheet, I used VLOOKUP and deployed the Product ID as the lookup value.I may have spoken too soon. This is definitely what I need, but I'm far too novice to apply the codes to my own work book. I'm not sure if you coded every column, or how you got them to fill in. Trying to look up tutorials, but so far I haven't been able to duplicate what you did. I might be too novice for this.So it took quite a bit of doing, but I managed to figure it out! Thanks!
What Abiola1 has done is to give you a Customer Quote sheet (or order form, or however you'd want to label it) into which the sales rep simply enters two pieces of information: the Product ID and the Quantity. And for Product ID he's used the General Price List to provide a comprehensive drop-down menu so as to avoid errors. (See Data Validation under Help for further explanation) On this attached, I've made it more obvious (a useful convention in my experience) by giving the entry fields a yellow background. The rest of the sheet is calculated for you by use of two VLOOKUP formulas and then simple multiplication (Quantity X Price).
He's also named the price list by using the Name Range ability, which you'll find under the main menu, Insert .... Name. ..... First highlight your entire price list, and then use that menu function to name the range.
From there VLOOKUP will find the information relevant to any given product ID #---I hope yours are simpler than his example, so they're easier to remember--.
You should research using Excel Help (or Google) the VLOOKUP function. The basic syntax is
VLOOKUP(Reference,TableToSearch,Col#,True or False) where
- "Reference" in this case is the product ID as found in Cells A5, A6 and so on down the page.
- TableToSearch, in this case, is the price list. You would use whatever name you apply to the range, as discussed above.
- Column tells the formula how many columns over, in the table named, to go. Col 1 in the sample is the product ID. Col 2 is the Product Description; Col 4 is the price.
- Where it says True or False, you enter False or 0, which tells the VLOOKUP formula to find an exact match for the product ID.
On the form itself, you'd want to design it so that the user out in the field has as many rows as the maximum he or she is likely to need.
That definitely looks like what I'd like to do!
Unfortunately I didn't see anything in that file that states how I would go about setting this up. I can certainly make the table, but how would I link the spreadsheets so that typing in one brings up information from the other?Oh I see what you did there! Sorry I didn't know you included an entire file with everything. I thought it was just an example. Thank you I think this will help!
