Forum Discussion
Pick & Choose numbers from connected Excel File
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.