If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dynamic DropDown list from Unsorted vector
This seems to be a fairly common question I have found...with no answer! Maybe I just missed the answer somewhere, but I am hoping to get a quick, direct, answer. I simply want my dropdown list to contain an array of fields from a column A if column B = "Yes". The problem is the that my rows are not sorted. Example: 0 A B 1 *num* *YN* 2 one Yes 3 two No 4 thre Yes On the same sheet, I have a cell which does Data Validation List. I just want a simple code for Source: to create the dropdown list that will only list (one, thre). I have gotten this to work, partially, a couple different ways...but nothing I have found works perfectly. It doesn't seem like it should be a complicated task... This code will only return a direct vector in order = returns 2 rows starting from first row of matched item. Returns (one, two)...thus requires a sorted list. =OFFSET(B1,MATCH("Yes",B1:B4,0)-1,-1,COUNTIF(B1:B4,"Yes"),1) This code will return only the first found single entry. Returns (one). Not exactly much of a list... =INDEX(A2:B4,MATCH("Yes",B2:B4,0),1) I would like to do this without having to dynamically create another list along side my table to show all "Yes" items only, leaving the rest blank to let excel Ignore Blank cells. I am fine with using a macro...but just not sure how I would do it. If there was a way to write the Data Validation completely as a VB macro, and not use the built in Data Validation, I could probably just use an IF statement to throw the range into an array, then populate the list from that... I don't know how to mix VB macro variables with the Data Validation, if that is possible. My actual sheet has well over 100 rows with 8 columns and multiple sheets in which this sample can be used to help me fix my full sheet. I am sure there is a simple answer and I am just missing it. But through all the searching online, I haven't yet found a clear cut simple answer! Any help is greatly appreciated. -- RegorTheGreat |
Thread Tools | |
Display Modes | |
|
|