Jun 28 2020 08:36 PM
Jun 28 2020 08:36 PM
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
Jun 28 2020 10:10 PMSolution
@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.
Jun 29 2020 12:28 PM - edited Jun 29 2020 01:07 PM
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.
Jun 29 2020 10:02 PM - edited Jul 01 2020 11:11 AM
@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.
Jul 01 2020 10:24 AM - edited Jul 01 2020 10:24 AM
@Riny_van_Eekelen thank you for all the information. Of course, people were impressed when I showed them what I am working on....I was asked to add one more column in this form that can show current pricing information for the products. Example-Select->Vendor->Product->PN pop ups and than pricing pop up column. Is this something I can do easily do I have edit your great formula?
Jul 06 2020 12:16 PM
@Riny_van_EekelenI thought I was done with this but not yet....lol
so, I have this order form where people can go and select items they want to purchase etc. This part is working good now with your help. Now, they also want to SAVE copy of the order on different worksheet (in same excel doc) so they can go back to it when items are received or they can look at if an item is order or not? Is this possible?