Geek asks and answers

EXCEL VBA - creating a dynamic drop-down list based on a range of cells and a row

Ok, so I have a drop-down list that populates out of range on a separate sheet.

I want to be able to reduce the values ​​in the drop-down list by adding only the values ​​that have a text value from another cell in each value text.

for example: Cell A1 says “table” Cell A2 is a drop-down menu that draws values ​​from a range. There is a “table”, a “large table”, a “chair” in the range

Currently, the dropdown menu will contain all the values. In this case, I would like it to list only the “table” and the “large table”, since they both have a “table” in them.

An example of my file can be found here http://www.filedropper.com/example_1

Please help .. I am tearing my hair!

Thanks Mike

+5
source share
2 answers

Here is one way to do this without VBA:

(all instructions must be followed on a sheet with a list of items, unless otherwise indicated): enter image description here

  • Search for text. In C2, type =NOT(ISERROR(SEARCH('Choose Items'!$A$2,A2)))and copy
  • Number of views in D2:, =IF(C2,1,0)in D3: =IF(C3,D2+1,D2)- copy down
  • Create a dynamic list: put the numbers 1-8 in F2: F9. In G2, use this formula: =IFERROR(INDEX($A$2:$A$9,MATCH(F2,$D$2:$D$9,0)),IF(F2=1,"No match",""))and copy.
  • Create a dynamic named range - in the Name Manager (Formulas-> Name Manager) add a new name ItemsDynamic and specify =OFFSET(Items!$G$2:$G$9,0,0,MAX(Items!$D$2:$D$9,1),1)as a formula.
  • ItemDynamic

! .

+1

.

"" , , " C", ( C2) .

=IFERROR(RIGHT($A2,LEN($A2)-FIND(" ",$A2)),$A2)

" ", B2, :

=OFFSET(Items!$A$1,MATCH('Choose Items'!$A2,Items!$C$2:$C$10,0),,COUNTIF(Items!$C$2:$C$10,'Choose Items'!$A2),)

, "B"

, "" 10, countif, ( "C" ) ).

, "" ( ), "C".

+2

More articles:


All Articles