Forum Discussion
Excel help with vlookup and more
Hello, I am trying to create an order form in Excel with 5 columns. First 2 columns are generic Item No. and Quantity. My 3rd Col. has Vendor information where a person can pick the vendor they want (drop down list). However, it also allows correct product info (items you can buy from that specific vendor) to popup in next column call Hardware Type. Example-if you have Apple, Samsung, Microsoft as vendor and you pick Microsoft, you will only see all the items you can purchase from Microsoft not from Apple or Samsung. This part is working fine and here is my problem----when I select an item in Hardware Type column, I want to automatically popup Part No. based on whatever hardware type person is selecting. Example-if I select Microsoft as vendor and in hardware type I select office 365, I want to have office 365 part no or product code automatically popup in Part No column. I can't get it working....any help in this matter would be greatly appreciated. if you need more info, let me know. Thanks
gdubey Attached. workbook with two possible solutions.
The first uses named ranges and INDIRECT. In the attached example the named ranges are static. Didn't go so far as to make them (more) dynamic since you indicated that you had everything working except for the part number lookup.
The second one uses new functions (UNIQUE, FILTER, XLOOKUP) and only works on the most recent version of Excel.
18 Replies
gdubey Attached. workbook with two possible solutions.
The first uses named ranges and INDIRECT. In the attached example the named ranges are static. Didn't go so far as to make them (more) dynamic since you indicated that you had everything working except for the part number lookup.
The second one uses new functions (UNIQUE, FILTER, XLOOKUP) and only works on the most recent version of Excel.
Riny_van_Eekelenman, you are the master...you have solved my issue. I really want to thank you for your suggestion and attaching this file for me. it gave me god idea what I need to do. much, much appreciated...keep up the good work 🙂
gdubey Glad you could figure it out before I had a chance to respond to your previous post. In case you are using Microsoft365 (previously known as Office365), you may find the second solution more flexible, though. The functions used should become available to most subscribers in the coming weeks.
Imagine you have hundreds of vendors and thousands of parts. Working with named ranges will soon become difficult, if not unmanageable. The second option allows you to put all the vendor and parts information in one big table, and run dynamic valuation lists from that.
since, I am horrible with Excel, I am trying to understand your response and data/form you sent to me. Thank you again
I am attaching my form in this well for you to take a look...
as you can see, if you select CISCO for vendor, select any hardware they sell, you will see the part no will change accordingly. BUT, it only works with CISCO!!! if I select different vendor, it doesn't work.
I haven't check your solution yet just got busy with work but I will check it out and let you know. I much appreciated your help and your quick reply.
