Excel Formula Help

msjhart87

New Member
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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|"?*"))

Thanks in Advance!!
 

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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
    ExcelA.JPG
    227.8 KB · Views: 3
  • ExcelB.JPG
    ExcelB.JPG
    70.7 KB · Views: 3

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 9, 2003
Messages
1,133
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 13, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top