-
How to automatically # rows with a formula?
I had a column which uses a formula referencing the value of the row below it and then add one. For example the row 2 value is [Opp Num]3 + 1. This formula automatically carried to any new entries for months allowing for continuous unique numbering of new rows. Recently this formula has not been working as intended as it…
-
Autopopulate Date based on Column
Hi, Looking for the formula that will automatically put the date when a column is checked "yes". It needs to be the date that the column changed to "yes", not todays date (as that will keep updating as days go on). When the column "Sent to Payroll" changes to yes, I want the date that it changed to "yes" to show in "Date…
-
Cross Project Dependencies
Hi SmartSheet Community! I have built a portfolio of projects and I want to create an additional project within the portfolio to capture action items that need to be completed before some of the tasks in the related projects could complete. Example, I'm making ice cream and I have three projects in the portfolio: Vanilla…
-
If/Index/Collect Across two sheets
I'm getting an invalid value error but I'm not sure why with the following formula: =IF([Journal Source]@row = "Supplier Invoice", INDEX(COLLECT({Forecast Sheet Row Number}, {Forecast Sheet Supplier}, Supplier@row, {Forecast Sheet Month}, [2023 Month]@row), 1)) Referenced Sheet: Original Sheet (With Formula in the matching…
-
Help on Or/Contains formula
I am in the need of some help as I am banging my head against the wall trying different formula options here. I wish to grab all characters left of a certain character combination (" -") from a drop-down cell. For instance, I wish to grab "Hosted Event (physical)" from the drop-down cell with "Hosted Event (physical) -…
-
Date validation formula referencing Sheet Summary fields
Hello, I'm trying to update a date validation formula to include a reference to two Sheet Summary fields. The formula seems to be working -- except for the piece that references these Sheet Summary fields (in bold): =IF(OR([Preferred Date*]@row = "09/04/23", [Preferred Date*]@row = "11/23/23", [Preferred Date*]@row =…
-
SUMIFS with Multiple Criteria
Hi all, Trying to SUMIFS with multiple criteria and getting an unparseable error. I need to know the sum of hours by a department for each month. =SUMIFS({Estimated Hours}:{Estimated Hours}, {Sprint}:{Sprint}, input@row, {Department}:{Department}, "Sales")
-
Risk Formula (Red, Yellow, Green)
I am using the formula below to automatically calculate risk. I am using percent complete of a task against time complete off the start and end dates. =IF([%CMPLT]@row >= (NETDAYS(TODAY(), [End Date]@row) / NETDAYS([Start Date]@row, [End Date]@row)), "Green", IF([%CMPLT]@row > 0, "Yellow", "Red")) So if my start and end…
-
Formula If/Then help
I have the following columns Confidence 2023 Calendar Savings I need to have a formula in a third column that calculates the following If confidence = 25 then multiply 2023 Calendar Savings by .25
-
% complete duration
I would like to calculate duration % complete from the actual start and actual end date, Is there a formula to calculate the duration, should be calculated based on hours (24 hours)