Forum Discussion
Help with Cascading drop downs and VLookup
Thank you for the input! There will be eventually be multiple classifications and price lists added. Can the IFERROR command be expanded to multiple classifications?
bphillipslloydaubry Yes, the IFERROR will work but you'll end up with many nested IF statement, which will get out of hand quite quickly. Would recommend a different set-up of your data. But I don't have the possibility right now to work on it. Give me some time, or hope that someone else picks-up this thread.
Riny_van_Eekelen Thank you Riny, I am not in a rush and appreciate your time.
See attached file. I have kept the structure of the Consumables sheet as you designed them, but introduced two helper columns on Sheet1 in order to keep the formulae manageable. These helper columns find the column and row number in the Consumables sheet, for the item select in Sheet1. Perhaps not the most elegant solution, but fairly easy to maintain. On the "Consumables" sheet, add CLASSIFICATIONS as indicated in A9 and just keep adding the part lists for each Class anywhere to the right of column A and create a Named Range of the first column in the new part list). Make sure that the 2nd column always contains UOM and the 3rd always $/UOM.
Avoid spaces and odd characters in the Names you give to ranges. They will get replaced by underscores "_" and the classification selected in the first dropdown will not be matched to a Named Range. IF a Name starts with a number, an underscore gets added in the beginning. Since your example had two of such ranges "304SS" an d"316SS", I built-in this possibility in the Data Validation rule for the "Description". The rule will automatically translate these to the Named Ranges "_304SS" and "_316SS". Read more about it here:
https://trumpexcel.com/named-ranges-in-excel/
Riny_van_Eekelen Riny, absolutely amazing, thank you!