-
Successors
Hi, I'm struggling with my formula designed to find Successors for each row based on the predecessors column. I've almost got it working, but as you can see from the attached photo, in cells using the formula, rows that include one of the rows are also added. For example, on row 3 (successors), 31, 32 and 33 are included,…
-
how to extract currency from a string of text
similar to a bank statement 07-22 Point Of Sale Withdrawal MARTYS FI 61 MAIN 13.99 - SHOPKINTON MAUS 07-22 Point Of Sale Withdrawal PAN THAI 44.38 - RESTAURANTHOPKINTON MAUS 07-22 Point Of Sale Withdrawal ALS STATE STREET 10.00 - CABOSTON MAUS 07-22 External Deposit Resilience03WBZk C:42320H:03WBZk - 35.00 DIR DEP I want…
-
Week Ending Date on Dashboard - Automatic Update
Hello, how can I leverage the metric widget in a project Dashboard to display 'Week Ending X/XX/XXXX' that updates automatically? For example, for the week of 11/8/21-11/12/21, I would want the widget to show Week Ending of last week (11/5). When it becomes 11/15/21, I would want it to display 'Week Ending 11/12/21'. Do I…
-
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) =…
-
COUNTSIFS does not Contain
Hello, I've put together the following formula, to count certain items in a different sheet that don't meet certain criteria, please see below. =COUNTIFS({SE-Region}, Region@row, {Status}, <>"Completed", {Status}, <>"Canceled", {Status}, <>"Duplicate", {Summary}, <>CONTAINS("Onboarding", {Summary})) The formula works until…