First of all, I do not need any help with this post, I just feel compelled to share the formula I developed.
I have been searching all over for a better way for data validation to work with dependent choices. Many of the posts on this subject refer to this site: http://www.contextures.com/xlDataVal02.html . This is a great and easy to use way to use dependent data validation but in my case, I would have had over 50 different range names and the data under each range changes from time to time. I what to create a list of item number that is narrowed down if the Product Code you are looking for is identified. Here is the formula I added to the Data Validation List for Item number:
Formula:Please Login or Register to view this content.
Basically what this formula does is it uses the Match function to find the first row with the identified product code and then it uses the CountIf function to count the number of rows to include on the data validation list. Keep in mind that you will have to sort the items by product code (in this case) for it to work properly. I attached a sample workbook for your reference.
Enjoy,
Jared
Bookmarks