-
Formula to add "No" to a blank cell, but leave value if not blank
On a Smartsheet project intake form, I have several Yes or No dropdown selections. If Yes, the user must answer additional questions. If No, they do not and those cells are left blank. Auditors do not like Blank cells, so I'm looking for a formula to replace those blank cells with "Does Not Apply" but leave a value there…
-
Calculate the time between two dates by days, hours and minutes
Hi, I am trying to implement the following formula from my excel spreadsheet into my smartsheet: =INT(O2-Q2)&" Days "&HOUR(MOD(O2-Q2,1))&" Hour "&MINUTE(MOD(O2-Q2,1))&" Minutes" Basically, i need to calculate the time between two columns (date received) and (date documents issued) in days, hours and minutes. Cell 'O2'…
-
Weighted Average With a Condition
Hi Is it possible to gather a weighted average, with a condition? My starting formula is but I would like to build in a condition. In other formulas this has taken the form of I can't work out how to bring these together / if it is possible. Grateful for any thoughts anyone may have! Emma
-
SUMIFS with LEFT as a reference
I'm looking to sum a range as long as 3 criterion are met. 1 of those criterion is that Left of another sheet reference (4 characters) match a cell (could be text in the formula as well). =SUMIFS({Time To Assemble}, {IPK}, [Machine Type]@row, {Progress}, "Finished", LEFT({Item Number}, 4), [Machine Type]$17) This is…
-
COUNTIFS formula that compares dates in two columns as a criteria
Here's what I'm trying to do. KPI is a check box field (Key Performance Indicators). Status is a single choice list box field. Actual Finish and Plan End date are date fields. I want to count the number of KPI rows with a check mark where the status is complete and the Actual finish date is on or before the plan end date.…
-
Analyzing Text for Special Characters in a Conditional Formula
I have two sheets. Sheet 1 (Character Table) has a list of non-alphanumeric text symbols (e.g., !, @, #, $, etc.) and alphanumeric characters (A-Z, a-z and 0-9) alongside their ASCII values. This table distinguishes the non-alphanumeric values from the alphanumeric ones by classifying their types as "Special" or…
-
Help with Updating Rows Via a Form Submission
Hello, I am trying to use Smartsheet to track reagents, the workflow is as follows: The person prepping the reagents fills out a form with the pertinent information and the number of vials made. in this example the person made 40 vials. I would like to have this same number decrease via form submission. Example.) 1 vial is…
-
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…