-
Lord Help Me!
Would appreciate some help with a formula! I ultimately need a column that either shows a count based on the difference between two dates, or a "0." In one column, we indicate whether a job post is open. "Yes" it's open, "No" it's closed. In another column, we show the date the job was posted. In another column, we show…
-
Formula to move a cell from one sheet to another based on conditions
Hi all, I'm currently using Zapier to move data from a google drive to a data source Smartsheet. During the transfer, sometimes there are multiple rows with the same claim numbers on the data source sheet (that part isn't going to change unfortunately). I have a separate summary sheet that that pulls the information I need…
-
Issue with Concatenate formula
I'm currently using the below formula, however it's abbreviating the first name rather than spelling out the whole name. I'm having a hard time identifying what I'm missing within the formula: =(LEFT([First Name]@row, 1) + "." + [Last Name]@row + "@gmail.com")
-
How to create a countif formula with OR
I am trying to create a formula across columns with different values in a cell. I have the formula for one value, Preliminary Draft. This works fine. =COUNTIFS({Comments Log Range 1}, [Document Name]@row, {Comments Log Range 2}, "Preliminary Draft", {Comments Log Range 4}, "Open") However, the value in range 2 can also be…
-
Count Rows Where A Value in Multiple Columns is blank
Is it possible to count how many lines meet certain criteria AND have a blank value in Column 1 OR Column 2 OR Column 3? For example I have a Smartsheet with a questionnaire and am currently trying to calculate metrics from the answers in said questionnaire. One of my metrics is "Level 1 Completion" (which is equal to "Q1…
-
Join Formula with Dates - How to eliminate the time stamp?
I am using the join formula to combine the task name, start date, and end date into a single column that will be used for reporting purposes. The start and end dates are formatted to only show the date, not a time, however when I use the join formula, it's giving me 'Task Name -- ##/##/## 8:00 AM - ##/##/## 4:59 PM" and I…
-
Auto check all check boxes in a column
Hello! I have a sheet with a check box column where end users select the check box to nominate a speaker which will trigger a separate workflow. The issue with individually selecting is that there can be upwards of 100 speakers on the list. Is there a way to have an option for the end user to click one check box or choose…
-
Stacked formula, Data, Symbol and if blank
Hi, I am trying to write a formula saying: If blank then blank If Status column has "Full" symbol Progression to Completed Date column is "Green" Or if Completion Date is greater than today is "Red" Or if Completion Date is less than 7 days is "Yellow" otherwise is "Green" So far I have this, that is working well for…
-
CountIFs using multiple criteria
I want a count of the number of tasks that are complete. I don't want to include anything marked as not applicable or any headings (Hierarchy=Grandparent or parent), and it is counted as complete whether it completed by Medhost or by facility. =COUNTIFS([Not Applicable]:[Not Applicable], 0, Hierarchy:Hierarchy, "Child",…
-
How do I use percentage formulas to breakdown responses
I am currently tracking a project and have 4 columns that use the following values - pass, fail - see notes, n/a, not tested. How can I break this down % wise to see which line items have passed, failed, n/a or not tested per column? The goal would be to roll the information up to a report then up to a dashboard.
