-
How you calculate the difference for the weeknumber of a date and todays date, considering the year?
I have a project schedule where we use =IFERROR(WEEKNUMBER([End Date]@row) - WEEKNUMBER(TODAY()), "") to calculate how far away the week number of the End Date is from the week number of Today's date. We have End Date's that are from Today's date just a year ago causing them to calculate a 0 as if the row item's End Date…
-
Why do the dates change in the fillable PDF?
Hello I am making fillable PDF and I was trying to put a birthday in them and when it loaded it came out as (Not real) 04/05/23. I have changed it in the smart sheet cells where the dates read out as April 5, 2023 but when it hits the fillable PDF it changes to just numbers. I need it to be spelled out does anyone know why…
-
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…