• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Load Dropdownlist if meet the criteria

saamrat

Member
My worksheet has a dropdownlist. If Room is vacant, then populate the dropdownlist with vacant rooms. Worksheet attached.
Please help
 

Attachments

  • dropdownlist.xlsx
    8.9 KB · Views: 13
hello ,This post must be moved to the vba section
try this Code
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err:
Dim current As String
Dim update As String
If Target = Range("B4") Then
current = Target.Value
update = "Room " & Application.VLookup(current, ActiveSheet.Range("Names"), 2, 0)
Target.Value = update
End If
Err:
End Sub
 

Attachments

  • dropdownlist.xlsm
    19.1 KB · Views: 28
Thank you very much for the help. Appreciated!
I am looking for a solution that can populate dropdownlist with Vacant Rooms(room numbers)
Any Help?
 
or with some helper cells/formulas.
[K1] ="Vacant" => as hard coded variable
[L1] = COUNTIF(F:F,K1) => the number of item that should show up in the list
[J2] = IF(ROW(E1)<=$L$1,INDEX($E$3:$E$12,AGGREGATE(15,6,ROW(OFFSET($E$1,,,COUNTA($E:$E)))/(COUNTIF($K$1,$F$3:$F$12)),ROW(E1))),"")
  • the IF ( ) checks if the actual position in the list exceeds the number of items. If so, the cell remains "empty".
  • INDEX($E$3:$E$12 is the range where the list needs to be extracted from (could be made dynamic or set to a large enough range)
  • ROW(OFFSET($E$1,,,COUNTA($E:$E))) constructs a dynamic range based on the number of rooms in the original list and contains a list of positions or "rows"
  • (COUNTIF($K$1,$F$3:$F$12)) notice the inverse of arguments: it gives the positions of "vacant" in the list
  • using a division between both gives {1,#DIV/0!,3,#DIV/0!,5,6,7,#DIV/0!,#DIV/0!,#DIV/0!}
  • AGGREGATE(15,6 will select the smallest value excluding the errors.
  • the exact position is returned via ROW(E1). Dragging down the formula and since the row reference is relative the position returned is always +1.
Finally in the name manager RoomDropDown =Sheet1!$J$2:INDEX(Sheet1!$J:$J,Sheet1!$L$1+1) is a dynamic range starting from J2 down to the number counted. Because there is a title in the way I add one to the counter.
Use this name in the drop down definition.
 

Attachments

  • Copy of dropdownlist.xlsx
    9.8 KB · Views: 12
Back
Top