If(SEARCH(....... statement with dropdown list....

[raineb]

Hi,

I'm wondering if someone could help me with my excel project.

I already have my formula drafted. But my problem is how the cell would have a drop down list if the word "MI1" is found on previous cell. Please see the formula in red.

=IF(ISNUMBER(SEARCH("CC1",H3)), "Payment", IF(ISNUMBER(SEARCH("VP5",H3)), "Payment", IF(ISNUMBER(SEARCH("PP12",H3)), "Payment", IF(ISNUMBER(SEARCH("PC8",H3)), "Payment", IF(ISNUMBER(SEARCH("PC9",H3)), "Payment", IF(ISNUMBER(SEARCH("RE1",H3)), "Payment", IF(ISNUMBER(SEARCH("AB2",H3)), "Balance Inquiry", IF(ISNUMBER(SEARCH("QB1",H3)), "Billing", IF(ISNUMBER(SEARCH("QB2",H3)), "Billing", IF(ISNUMBER(SEARCH("QB3",H3)), "Billing", IF(ISNUMBER(SEARCH("QB4",H3)), "Billing", IF(ISNUMBER(SEARCH("CB6",H3)), "Billing", IF(ISNUMBER(SEARCH("SR7",H3)), "Billing", IF(ISNUMBER(SEARCH("SR8",H3)), "Billing", IF(ISNUMBER(SEARCH("CB7",H3)), "Insurance", IF(ISNUMBER(SEARCH("IU6",H3)), "Insurance", IF(ISNUMBER(SEARCH("IU7",H3)), "Insurance", IF(ISNUMBER(SEARCH("SI3",H3)), "Insurance", IF(ISNUMBER(SEARCH("VC10",H3)), "Insurance", IF(ISNUMBER(SEARCH("AT3",H3)), "Legal Request", IF(ISNUMBER(SEARCH("AT4",H3)), "Legal Request", IF(ISNUMBER(SEARCH("AT5",H3)), "Legal Request", IF(ISNUMBER(SEARCH("MI1",H3)), "Drop down list", IF(ISNUMBER(SEARCH("RI1",H3)), "Open To-Do/ff-up",""))))))))))))))))))))))))



What I need is that the cell would only show the dropdown list if it found the word "MI1" on the previous cell, otherwise, no dropdown list should be shown....

Hope to hear an answer...

Thanks in advance...

[tigeravatar]

raineb,

Welcome to the forum!
Unfortunately, what you're asking for cannot be done with a formula. It would require VBA (macro).
However, because your formula was making my eyes bleed, here's a cleaned up version:

=IF(SUM(COUNTIF(H3,"*"&{"QB1","QB2","QB3","QB4","CB6","SR7","SR8"}&"*"))>0,"Billing",IF(SUM(COUNTIF(H3,"*"&{"CC1","VP5","PP12","PC8","PC9","RE1"}&"*"))>0,"Payment",IF(SUM(COUNTIF(H3,"*"&{"CB7","IU6","IU7","SI3","VC10"}&"*"))>0,"Insurance",IF(SUM(COUNTIF(H3,"*AT"&{3,4,5}&"*"))>0,"Legal Request",IF(COUNTIF(H3,"*AB2*")>0,"Balance Inquiry",IF(COUNTIF(H3,"*RI1*")>0,"Open To-DO/ff-up",IF(COUNTIF(H3,"*MI1*")>0,"Drop down list","")))))))

Note the formula could be even shorter if you had a reference table with what to look for and what the result should be.
If you are interested in a VBA solution for the drop-down list, let me know

[raineb]

Thanks for your quick response tigeravatar.

Sorry to make your eyes bleed... ^^ haha... It's my first time doing excel on my own...

Yes, I'm very much interested in VBA solution... Hope you could help me...


Thanks much in advance...

[tigeravatar]

raineb,

Attached is an example workbook based on the criteria described.
It has two worksheets, "Sheet1" and "Lists"

In sheet "Sheet1":
Row 2 is a header row so actual data starts on row 3
Column H is where you can enter the various items shown in the formula (QB1, CB7, VC10, RE1, MI1, etc)
Column I is the result based on what you chose/entered in the corresponding row in column H. Column I is governed by the macro code provided below.

In sheet "Lists":
Row 1 is a header row so actual data starts on row 2
Column A is the list of items that "Sheet1" column H can accept
Column B contains the various results for the entries available for "Sheet1" column H
Column A is defined by a dynamic named range with name "listHeaderH" whose formula is:

Please Login or Register  to view this content.

Column D contains the drop down list that will be provided when a user selects "MI1" as the entry in "Sheet1" column H
The first line in the drop down list is "Select Item" so that the user will know that it is a drop-down list
This is further emphasized with conditional formatting on "Sheet1"
The drop-down list is also defined by a dynamic named range with name "listItems" whose formula is:

Please Login or Register  to view this content.

(To view the named ranges, go to the Formulas tab and click Name Manager).

Lastly, in the "Sheet1" code module this worksheet_change event code:

Please Login or Register  to view this content.

To view the code, press Alt+F11 to open the Visual Basic Editor (VBE) and then double click on Sheet1.