Ok, I need help writing a formula (=IF) for a drop down menu. Here’s what I’m trying to accomplish Have a drop down menu where you select a number 1 through 4. Once that is selected, it shows you a list of available labor operations where that condition is met. It’s basically like filtering the cells without seeing the whole list. The only hang up is where the number 1 through 4 is populated, some cells contain 1,2,3,4 and need to be included in the list as well no matter what number is selected. Lane 1 General Shop - 1 Pump & Valve Assembly - 1,2,3,4 Thanks, Brian
What you're asking isn't close to a one (or even two) step process. You would need to have some pretty clean and consistent data and if you're working with lots of rows that could prove time-intensive if you aren't familiar with lookup/reference and text-based formulas. Why not just use the normal filter feature on your data and then use the "Text Filter"->"Custom Filter" functionality. You can then filter on specific conditions (contains/does not contain/equals/does not equal etc), using both "and/or" arguments to find any combination of 1/2/3/4 as needed.
Always start with the data. The comment about cells containing 1,2,3,4 lead me to think this will be a mess. That’s a violation of 1st normal form and will cause endless problems. I’m thinking he needs to clean and restructure the data first. The easiest way to do this off the top of my head would be to create a table of options with the input and return values and normalize it, then use the dropdown (drop downs?) to lookup and get the right value. Hard to say without seeing the actual problem, but generally it’s always going to be much easier to contain complexity in data than try to code it.
Echoing what they said. Excel doesn't do well with strings in a single sell - you need to break each step into its own column and then it's beyond simple. If you want some troglodytes to be able to see "1,2,3,4" in a cell at the end, just concatenate it all after the fact. Rereading your OP, it's prolly as simple as just getting your data structure correct and then throwing a filter on that biatch.
post a screenshot of the sheet i'd say IF is a bit too weak for what you are attempting. --> after one minute thinking about it and considering recommendations, i said F IT and wait what the real pros have to say
Here's a way to do it but it does require one extra step, apologies for the mess but I threw this together pretty quickly: Yellow = Input, Blue = Formula. B1 has data validation to pick from a list of F3 to I3 I made up some extra steps in there to validate the formula works F through I are columns that is intended to create a column of all activities that contain that number, and leave a blank if it doesn't contain that number. The formula in F3 is "=IF(ISNUMBER(SEARCH(F$2,$E3)),$J3,"")" Once that full table is set up that way so that there is a dummy column that blanks out activities that aren't in that number, the thing that gets the job done is in A4, which is "=UNIQUE(FILTER(INDEX(F3:I9,0,B1),INDEX(F3:I9,0,B1)<>""))" This spits out everything in the filtered column and eliminates any blanks. Kind of a rough solution but it works without modifying the data you mentioned, it just requires one extra step of making those specific columns.
Cancel that, I spoke too soon, I figured out a one step process: A4 formula is "=UNIQUE(FILTER((IF(ISNUMBER(SEARCH(B1,E3:E9)),F3:F9,"")),(IF(ISNUMBER(SEARCH(B1,E3:E9)),F3:F9,""))<>""))" B1 will need to be set as a manually typed list for data validation unless you have the numbers existing somewhere else. E3:E9 = The array with the step numbers in it F3:F9 = Step names
Ok, out of 64 rows, only 5 contain the 1,2,3,4 string which I can remove since they're not used very often. I'll mess with writing the IF statement to return a list if the qualifier is met column M to show the value in column A. I'm just trying to see all of the things in "lane 3" when the (3) is selected in my drop down. This sheet will reside elsewhere and not be on the sheet the rest of the team will be working on but that's not a big deal.
My above formula will still work, replace B1 with the drop down menu cell, replace E3:E9 with M?:M? and F3:F9 with A?:A?, replacing question marks with the first and last row numbers. As written, the formula basically looks for the number in B1 in each cell the E columns and then will return a list of corresponding cells in the F column, so even if it's just a single number or the number is in a list it'll pick up the step.
