-
Issue with Calculating Averages from Linked Cells in Reports
I'm experiencing an issue with calculating averages in a Smartsheet report. The cells in my source sheet are linked from another sheet, and when I try to use the AVERAGE function in the report, the result shows as "NaN". It seems that Smartsheet might not be recognizing the linked values correctly. Is there a way to…
-
INDEX/MATCH Partial Match
I am trying to pull the tech level for a hearing aid based on a master list. The issue is that my source sheet has a simplified name of the hearing aid that is on the sheet I need a the tech level. I've tried multiple variations of INDEX MATCH and COLLECT and am not getting it to pull. Formula Sheet: Source Sheet:
-
Dropdown multi-select columns list comparision
I have two columns that are dropdown multi-select. One column includes a list of all possible values (example: letters A-Z) while the second column includes a list of some letters (without duplicates (example: A, B, C). I would like to create a column that displays the missing letters (example: D-Z). How would I go about…
-
Help with COUNTIF Formula
Hello, I am attempting to get my formula to count a status only when it's in NSP (the operational company/OPCO) with a status of "Lumen Chg Order Complete" and the checkbox for 'Test & Turn Up Scheduled' is checked. This is what I have attempted in the formula that is returning UNPARSEABLE as a result: =COUNTIF({OPCO},…
-
How to copy the row number in a column.
How to copy the row number in a column. In attached snap, i want to copy the row Number 8 under TestIDID column automatically. Do we have any pointers for the same.
-
Why is the Date function not working?
I want to calculate the first day of a month based on the "created date" column. I tried using Year Function to get the year, then the Month to get the month and last column calculates =Date(([Yearcolumn]@row),([Monthcolumn]@row),1) Yet I get the #INVALID COLUMN VALUE year and month formulas are set on number format What I…
-
COUNTIF referencing another sheet and include a greater than/less than
I'm trying to include a greater than/less than in formula: =COUNTIFS({SourceInquiryUrgency}, "U1", {SourceInquiryUrgencyTable}, "Yes", {SourceAge}, >=31, {SourceAge}, < = 62) I already have a similar formula returning a count of the urgency category: =COUNTIFS({SourceInquiryUrgency}, "U1", {SourceInclude}, "Yes",…
-
Checking if Time Exceeds 24 Hours
Morning! =IF(SETA@row = "", "", IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) >= 12, IF(FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA Minutes]@row) / 60, 1) > 12, (FLOOR((VALUE(LEFT(SETA@row, 2)) * 60 + VALUE(RIGHT(SETA@row, 2)) + [ETA…
-
Showing multiple dates from the same sheet row in a calendar
I have 6 columns in my Smartsheet that are date columns, two of which are start and end dates. I have got the start and end dates to appear in a calendar report, but the other ones (individual dates, not date ranges) do not show up. I cannot have them as children rows. I have tried applying conditional formatting to the…
-
Help!! CountM but Distinct
Hello All, Our org uses multi-select columns a lot, and would like to know the total # of unique options ever selected within a range. In the example below, this would mean out of those 4 rows, I would like a formula that returns "3", meaning that 3 unique options were selected, rather than 5 total options as the =countm…
