-
Formulas - Can you exclude rows from average calculations?
I am looking for the parent row to reflect a rollup of child row status color (RYG) AND if ALL child rows are blue (complete), have the parent row blue. I'm almost there, however, in the screenshot example below, the parent task is yellow although the 3 child tasks are either green (on track) or blue (complete). Since the…
-
WEEKNUMBER from two columns
Hi All, I have two columns where one column will have a DATE and the other one will be blank. It will never happen that both columns will have a date in them. Now I want Smartsheet to check which two cells has a Date and show WEEKNUMBER.
-
Capture Timestamps Before They Change
Is there a way to save a date that something occurs? We have rows that get changed constantly, and the status balls change from Green to Red to Yellow to Gray. Any way we can capture the date that a row's status ball changes to Red, even if it later changes to other colors? Thank you!
-
return fiscal quarter from auto generated date column
Hi, this formula returns a blank - what is wrong? =IF(OR(MONTH([Created Date]9) = 8, MONTH([Created Date]9) = 9, MONTH([Created Date]9) = 10), "Q1FY " + YEAR([Created Date]9), IF(OR(MONTH([Created Date]9) = 11, MONTH([Created Date]9) = 12, MONTH([Created Date]9) = 1), "Q2FY " + YEAR([Created Date]9), IF(OR(MONTH([Created…
-
SUMIFS
I am trying to sum accounts receivable for individual salesmen. I have gotten to the point where I can add if the check box under [Check Received] is marked but I do not know how to sum if a check box is unmarked. So far my formula is returning 0 when it should be returning a over $100,000. This is my formula so…
-
Need an HLOOKUP or similar
Does anyone have a formula that will evaluate all the cells in a row, then return the column name for the cell that has a particular value? Here's what I am trying to do. I have a Smartsheet with 23 columns, of which 5 are due dates. A 6th date column is "Next Due Date" and contains a formula that evaluate the other 5 date…
-
COUNTIF Using Open-Ended Range Formula (Not Working)
I'm using the COUNTIF formula and am trying to set an open-ended range formula (see below). When I do so, I receive the error #UNPARSEABLE =COUNTIF(Test Result:Test Result, Status1) Is there any reason this wouldn't work? I've attached a screenshot of how Test Result is defined.
-
% complete based on count of completed pieces
Hi, I'm running a project where I want to report our the % completion of a sub task based on the # of pieces completed. I'm thinking I need to create a formula as the % of completion functionality looks at duration vs. actual amount of work completed. Can someone offer guidance?
-
Maximum cross-sheet references
Once you reach this figure of maximum cross sheet references how can you get around this. I want to configure calculations to 2030. Got to June 2025 before I got stuck. Regards Steve
-
Sorting a sheet when opened
Wondering if there is a way to sort rows of a sheet on open. Otherwise you have to set this up manually each time. If this is a smart sheet you would think it would be able to do this.