-
Can I sum two cells together and then clear the contents of one cell without changing new total?
I have two cells that I would like to sum together based on a check box. When Order Received is checked, Quantity Delivered is then sent into my total inventory in row 1. I would then like to clear all of the contents of row 2 through an automation, because that order is complete and done with, without changing my new…
-
Formula to COUNTIF with respect to the date column
These two formula are not working - ERROR - #unparseable. The solution I am looking for is to count the no. of MOUs in a calendar year (YTD 2023). =COUNTIFS({MOU Status}, "Active", {Date of execution}, (@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 12, 31))) =COUNTIFS({MOU Status}, "Active", {Date of execution},…
-
Help with WEEKNUMBER formula
Hi, I need a column to show the week number of the year. When I try the simple =weeknumber(TimeCardEntryDate@row) formula, it doesn't seem correct, but I've seen other posts about how Smartsheet calculates week numbers uniquely. I need the weeks to count from Sunday-Saturday. So, in the picture below, 12/31/23-1/6/24…
-
INDEX MATCH W/ 2 criteria and partial match
If I enter "HII-1607302" on a separate sheet I want it to search the Project Number for that partial match and return the value in the Budgeted Multiplier Column into a cell. Two issues: The Project # (HII-1607302 for this example) is only a portion of the text in the Project column, so it needs to be a partial match not…
-
Returning averages using associated values among several datasets
I'm not completely sure how to explain it...I'm building a sales dashboard, and I need to calculate several different averages associated to certain associates (volume, units, etc). The data I receive isn't formatted in the easiest way, so I'm looking for a formula that: finds each time the associate's name is listed in…
-
How to replace an SOP in a row when that row is updated
Hi everyone! I am creating an SOP Tracking system for my company where I will track all SOP’s being created. That being said, I need to be able to replace an SOP when the SOP is updated. Can I do this using either forms or automation? The SOP will have a specific name associated with it that will be able to be matched when…
-
Copy Row automation brings original sheet 'Created date' instead of showing date it was moved
Please help, I have a helper sheet that I am using to capture time stamps of when a row goes to different statuses (ex: capture time stamp when request status changes from: In Review, Pending Response, and Completed). To do that I set up a copy row automation that triggers when those statuses change. Then it copies the row…
-
Pulling selective data from a smartsheet and put it into columns of another smartsheet
So I have two smartsheets Portfolio Sheet Project plan In the portfolio sheet, I have the row 1. It has Columns StartDate and EndDate in that row. In the portfolio sheet, on that same row there is a Column called "Project Plan" that has a hyperlink to the actual project plan. Once that link is created in that column, I…
-
Why is this formula #invalid operation
=COUNTIFS(Flight:Flight, "FSC", [Date of Award]:[Date of Award], >1 / 1 / 2023, [Date of Award]:[Date of Award], <3 / 30 / 2023) If I put the dates in parenthesizes it yeilds 0 even though I have "FSC" in the Flight column. If I take them away it becomes #invalid.
-
Formula that counts # of incidents across a date range
I have a sheet for all incidents in the company that was started in 2023 and now that we're in 2024 I need to update some formulas so it only pulls the data for the current year. Right now the formula is =COUNTIFS({Incident Type}, [Primary Column]@row) but this doesn't take the date range into consideration. What should…