Help with Cascading drop downs and VLookup

Question

I am attempting to create a Material Requisition Price Sheet. I have created a reference table with necessary data and a seperate sheet with the drop downs. I was able to get the drop downs figured out but am having issues with VLookup. It seems VLookup can only pull from one column on the reference table, is this correct? Any suggestions that would allow the drop down electionss to pull the UOM and $/UOM from th ereference table?

riny_van_eekelen · Answer

bphillipslloydaubry&nbsp;Made some slight modifications to your sheet for both Data Validation and VLOOKUP formulae.My gut feeling says that this can be further improved, but haven't had the time yet to go "all the way". If this schedule fairly represents your actual needs (only two different classifications) , it might work just fine for you.

bphillipslloydaubry · Answer

Riny_van_Eekelen&nbsp;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?

riny_van_eekelen · Answer

bphillipslloydaubry&nbsp;Yes, the IFERROR will work but you'll end up with many nested IF statement, which will get out of hand quite quickly. &nbsp;Would recommend a different set-up of your data. But I don't have the &nbsp;possibility right now to work on it. Give me some time, or hope that someone else picks-up this thread.&nbsp;

bphillipslloydaubry · Answer

Riny_van_Eekelen&nbsp;Thank you Riny, I am not in a rush&nbsp; and appreciate your time.&nbsp;

riny_van_eekelen · Answer

bphillipslloydaubry&nbsp;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/&nbsp;&nbsp;&nbsp;

Forum Discussion

Help with Cascading drop downs and VLookup

6 Replies

Resources