-
Need formula to fulfill requirement where SS doesn't control input
I need help building a formula that looks at an adjacent cell and returns something (X or 1 or whatever) for the logic shown below: If content in the cell is not alphanumeric( e.g. special characters - neither a letter nor a number). And/or content in the cell is fewer than 3 characters or greater than 8 characters. It…
-
Stop counting blank cells
I have a column of checkboxes. Currently there are 16 rows with data but 18 rows total. I have a column setup for some of these called Skip which checks a box off so another function will know to skip those (Currently two rows are being skipped which is giving a count of 16). However when I want to count the blank…
-
Perform formula if value is found on another sheet
Hello Smartsheet users, I am attempting to create a sheet that will provide me with Weekly Usage totals of an inventory item so I can use the data to create better forecasts and projections. There might be a better way of doing this but as of right now my plan is as follows: Active Sheet contains all of the current totals…
-
Nested IF to populate Cell
Hi, I have a nested If formula I am having trouble with, =IF(Category@row = "Drink", "Lemonade, Tea, Water, Soft Drink", =IF(Category@row = "Toping", "Pepperoni,Steak,Chicken", IF(Category@row = "Fries", "Cheese, Chili"))) I have a drop in the Category column. If I choose the "Drink" option is is working fine, but if I…
-
Compare two sheets; flag a name that has not submitted a form
I'm trying to flag individuals who have NOT submitted a form, but am coming up short on the formula. Sheet A Memberlist has a list of all the individuals that need to submit the form. Sheet B Feedback is the form data after it has been submitted. I want to use Sheet A Memberlist as the reference for who is supposed to…
-
COUNTIFS formula for projects archived this year for a contact in a multicontact column
I'm trying to write a cross-sheet formula that counts the number of projects one lead (eg, Ben Canada) has archived in a given year. Here is a formula that works if Ben Canada is the only contact: =COUNTIFS({Rollup Sheet Example-Lead}, CONTAINS("Ben Canada", @cell), {Rollup Sheet Example-Archived Date}, YEAR(@cell) =…
-
Summarize child information in parent row?
Hi - I'm trying to write a formula that would group and summarize information from children rows into a cell in the parent row and I keep running into issues. I have a "Dropdown (Multiselect)" column for "Work type" that has the available values of Web Design, Copy, Video, Animation. Any row can be one or any of those…
-
Conditional Formatting with #No Match
Hi everyone, I've been working on conditional formatting utilizing the #no match on a formula (a vlookup that doesn't have a reference so we can add). I've like to have some cells highlighted (rows 1-12) when "no match" occurs, which is a possibility on the conditional formatting options, but highlights aren't happening.…
-
Formula to check box in sheet A based on data in sheet B
Will you help me write a formula in Sheet A that will do the following three things? 1) Look for a checked box in Sheet; 2) if the box is checked, then look to see if the cell in the same row - next column contains "100%"; 3) if both are true,
-
How do I extract data from a column that represents a week or month?
Lets say I count apples sold and I input the data in column [Number of apples sold] which is next to a date column. Now I want to find out how many apples I sold in a given week. I use this formula: =SUMIFS([[Number of apples sold]:[Number of apples sold], Date:Date, WEEKNUMBER(@cell) = 44, Date:Date, YEAR(@cell) = 2021)…