# Excel Formula Help

#### msjhart87

##### New Member
Hi guys, I am using this formula in Excel 2016 [ =IF(ISNUMBER(SEARCH(Catalyst!\$L\$3|K2))|MAX(\$J\$1:J1)+1|0)] - My Column J

It works perfectly, except I need to use it in other cell, if I remove the \$ around the L3 it stops functioning properly.

I used this formula to build a dynamic list; so based on the Value of Catalyst!\$L\$3 My list of 972 choices is shrunk into X.

Each row of my main spreadsheet could have a different L3 value needing to be entered.

Help please this is my last step and I have been stuck for a days trying to tweek this.

My Column M is based off the above formula [ =IFERROR(VLOOKUP(ROWS(\$M\$2:M3)|J2:K974|2|0)|"") ]

My data validation for L3 is =OFFSET(\$M\$2|||COUNTIF(\$M\$2:\$M\$973|"?*"))

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### KRice

##### Well-known Member
It works perfectly, except I need to use it in other cell, if I remove the \$ around the L3 it stops functioning properly.
What does this mean? If appears that you want to keep the cell \$L\$3 locked since your data validation formula is tied to that cell. I suspect you may want to lock the column in your MAX function...that is, MAX(\$J\$1:J1) becomes MAX(\$J\$1:\$J1). And quite possibly the K2 probably needs to be locked to the K column \$K2. But without seeing the layout of your spreadsheet, it is difficult to advise further. You can upload a small example of your sheet with the XL2BB add in.

#### msjhart87

##### New Member
What does this mean? If appears that you want to keep the cell \$L\$3 locked since your data validation formula is tied to that cell. I suspect you may want to lock the column in your MAX function...that is, MAX(\$J\$1:J1) becomes MAX(\$J\$1:\$J1). And quite possibly the K2 probably needs to be locked to the K column \$K2. But without seeing the layout of your spreadsheet, it is difficult to advise further. You can upload a small example of your sheet with the XL2BB add in.
On the Attached Excel B for Customer A - if I type any "aa" then the drop down list shows 3 options (Column H and I). Now for Customer B, only the options from Cell D2 appear, I am trying to get it where that each customer's input pulls up a drop down list based off partial input, does that make sense?

#### Attachments

• ExcelA.JPG
227.8 KB · Views: 3
• ExcelB.JPG
70.7 KB · Views: 3

#### KRice

##### Well-known Member
No, I still don't understand the objective.
For Customer A, do you want to enter a text string in cell D2 and then generate a list starting in H2 and down of all items in column G containing that text string?
Do you want to do something similar for Customer B, except the Customer B text string for filtering would be entered in cell D3 and the returned list would be in I2 and down?

#### KRice

##### Well-known Member

Like this?
MrExcel20210313.xlsx
CDGHIJKL
1Customer ACustomer BCustomer CCustomer DCustomer E
2Input AreaList of Optionsaaredbcccbbb
3Customer AaaBlue ABlue AAARed ABlue AYellow CCCGreen BBB
4Customer BredBlue BYellow AAARed BBlue B White BBB
5Customer CbBlue CRed AAARed CBlue C Black BBB
6Customer DcccBlue AAA Red AAABlue AAA
7Customer EbbbYellow A  Yellow B
8Yellow B  Green B
9Yellow CCC  Green BBB
10Yellow AAA  Red B
11Green B  White BBB
12Green D  Black A
13Green BBB  Black C
14Red A  Black BBB
15Red B  Black D
16Red C
17Red AAA
18White CC
19White BBB
20Black A
21Black C
22Black BBB
23Black D
msjhart87
Cell Formulas
RangeFormula
H2:L2H2=INDEX(\$D\$3:\$D\$7,MATCH(H1,\$C\$3:\$C\$7,0))
H3:L23H3=IFERROR(INDEX(\$G\$3:\$G\$23,AGGREGATE(15,6,(ROW(\$G\$3:\$G\$23)-ROW(\$G\$2))/ISNUMBER(SEARCH(H\$2,\$G\$3:\$G\$23)),ROWS(H\$3:H3))),"")

#### msjhart87

##### New Member
I have a drop down list with 972 items to choose from, for customer A I would like to manually start typing and the drop down list only displays items that contain the text I’ve entered. What I have works for customer A but when I try to type in the drop down list for customer B (which is row 2) it’s still only using the text I’ve entered in Customer A’s drop down.

#### msjhart87

##### New Member

I have a drop down list with 972 items to choose from, for customer A I would like to manually start typing and the drop down list only displays items that contain the text I’ve entered. What I have works for customer A but when I try to type in the drop down list for customer B (which is row 2) it’s still only using the text I’ve entered in Customer A’s drop down.
Like this?
MrExcel20210313.xlsx
CDGHIJKL
1Customer ACustomer BCustomer CCustomer DCustomer E
2Input AreaList of Optionsaaredbcccbbb
3Customer AaaBlue ABlue AAARed ABlue AYellow CCCGreen BBB
4Customer BredBlue BYellow AAARed BBlue B White BBB
5Customer CbBlue CRed AAARed CBlue C Black BBB
6Customer DcccBlue AAA Red AAABlue AAA
7Customer EbbbYellow A  Yellow B
8Yellow B  Green B
9Yellow CCC  Green BBB
10Yellow AAA  Red B
11Green B  White BBB
12Green D  Black A
13Green BBB  Black C
14Red A  Black BBB
15Red B  Black D
16Red C
17Red AAA
18White CC
19White BBB
20Black A
21Black C
22Black BBB
23Black D
msjhart87
Cell Formulas
RangeFormula
H2:L2H2=INDEX(\$D\$3:\$D\$7,MATCH(H1,\$C\$3:\$C\$7,0))
H3:L23H3=IFERROR(INDEX(\$G\$3:\$G\$23,AGGREGATE(15,6,(ROW(\$G\$3:\$G\$23)-ROW(\$G\$2))/ISNUMBER(SEARCH(H\$2,\$G\$3:\$G\$23)),ROWS(H\$3:H3))),"")
Yes is it possible to use this in a drop down list?

#### KRice

##### Well-known Member
Yes, but where are you placing these dropdown lists? It sounds as if you want to filter column G based on different criteria for each customer, and that may produce different lists (shown in the examples above in columns H:L. How many customers are you planning to do this for? The example is set up for five.

#### KRice

##### Well-known Member
Something like this allows the user to filter the main list for each customer based on the column D inputs. Then the helper columns in H:L build the filtered lists that are referenced in the drop down list selection box. To do that, go to where you want the drop down list to appear (I chose the cell beside each of the main filter inputs) and go to Data > Data Validation > and then under the Setting tab choose Allow: List and then in the Source field, enter the formula shown in the example here, adjusted accordingly for your ranges.
MrExcel20210313 (version 1).xlsb
CDEFGHIJKL
1Main ListCustomer ACustomer BCustomer CCustomer DCustomer E
2Filter Main ListDropdown ListsList of Optionsaaredbcccbbb
3Customer AaaYellow AAABlue ABlue AAARed ABlue AYellow CCCGreen BBB
4Customer BredRed CBlue BYellow AAARed BBlue B White BBB
5Customer CbYellow BBlue CRed AAARed CBlue C Black BBB
6Customer DcccYellow CCCBlue AAA Red AAABlue AAA
7Customer EbbbWhite BBBYellow A  Yellow B
8Yellow B  Green B
9Yellow CCC  Green BBB
10Yellow AAA  Red B
11Green B  White BBB
12Green D  Black A
13Green BBB  Black C
14Red A  Black BBB
15Red B  Black D
16Red C
17Red AAA
18White CC
19White BBB
20Black A
21Black C
22Black BBB
23Black D
msjhart87
Cell Formulas
RangeFormula
H2:L2H2=INDEX(\$D\$3:\$D\$7,MATCH(H1,\$C\$3:\$C\$7,0))
H3:L23H3=IFERROR(INDEX(\$G\$3:\$G\$23,AGGREGATE(15,6,(ROW(\$G\$3:\$G\$23)-ROW(\$G\$2))/ISNUMBER(SEARCH(H\$2,\$G\$3:\$G\$23)),ROWS(H\$3:H3))),"")
Cells with Data Validation
CellAllowCriteria
E3:E7List=OFFSET(\$H\$1,2,MATCH(\$C3,\$H\$1:\$L\$1,0)-1,COUNTIF(INDEX(\$H\$3:\$L\$23,,MATCH(\$C3,\$H\$1:\$L\$1,0)),"?*"),1)

#### msjhart87

##### New Member
Yes, but where are you placing these dropdown lists? It sounds as if you want to filter column G based on different criteria for each customer, and that may produce different lists (shown in the examples above in columns H:L. How many customers are you planning to do this for? The example is set up for five.
Each customer can have any of the 972 choices they are not customer specific. 100+ customers.

Replies
1
Views
103
Replies
0
Views
71
Replies
4
Views
88
Replies
10
Views
187
Replies
2
Views
127

1,129,795
Messages
5,638,382
Members
417,025
Latest member
MusterDuster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back