-
Simplifying text to another column
Hello! I think I need some sort of IF/OR/AND/THEN formula for what I'm working on. I am working in a sheet that has a status column with 8 different text values. My team wants to simplify the look of this in our external dashboard, so are creating a new column that changes the 8 distinct text values into 4 different…
-
MAX and COLLECT to find the latest date with specific criteria?
I have two sheets. The first sheet (called "Directory") lists the names of the organizations we work with (in a column called "Organization"). Another column (called "Date Last Journal") is supposed to display the date that is the latest date an employee interacted with an organization. The second sheet (called "Complete")…
-
Multiple index(collect... criteria formula
I am a Smartsheet beginner and hit a bind as I believe what I am seeking is fairly complicated, but I believe the program can do it. I have three sheets that have the same set up for reporting across three different projects. I want a MASTER file that will auto populate from these three sheets based off the name selected.…
-
Cumulative risk score per day between dates
I have a number of events, each with a start date and an end date. Each event has a risk score that is the same throughout the event duration. I would like to be able to show the total risk score for each individual day of the year, as multiple events may contribute to a venue’s risk. Thank you in advance!
-
Formula - 30 days/60 days Automatically
Is there a formula that will calculate 30 days, 60 days, & 90 plus days for aged AR without having to change the date range each month? I don't want to change the months on each formula each month. This is what I'm using right now. =SUMIFS([Invoice Amount]:[Invoice Amount], [Paid in Full]:[Paid in Full], "", [Project…
-
Sumif formula with checkbox reference
Can someone provide a second look and explain why the formula isn't working the way I believe it should based on formula logic. I'm having an issue with a sumifs adding my dep amt when the cleared check box is checked. Below is my formula and my input and out put sheet. It adds it when it is unchecked. If I change my…
-
INDEX MATCH and COLLECT all at once
I have used INDEX({Array},MATCH(Value in Column),MATCH(Value in Row)) to retrieve a value by matching the column and row I have used INDEX(COLLECT(Reference Column, Criteria 1, Criteria 2)) to collect What I am trying to do now is retrieve a value from an array based on the values in two columns and one row I have a sheet…
-
Automation erroneously triggered
My question is how can the ZZ 10 Day Indicator col (see below) become starred if the Package Complete Date column and the ZZ Completed 10 Days Date is not populated? When this is starred it moves the row t an archive datasheet - and in this case it moved it prematurely, erroneously, and I can't for the life of me figure…
-
JOIN(COLLECT
Hi, I'm trying to use the join collect formula (example of my formula currently) =JOIN(COLLECT({Meeting Minutes Topic & AI}, {Meeting Minutes Pursuit #}, [Pursuit #]@row, {Meeting Minutes Updated Date}, <TODAY-7, "-") Objective: I want the formula to search the meeting minutes sheet (pic 2) to match the pursuit # column…
-
Recreating Report in a Sheet
Hello! Our team has a financial tracker and our timesheets in Smartsheet, and I am trying to find a way to link them. The goal is to take the number of hours worked on each project, and multiply them by our average rate to get a rough estimate of our current labor cost. Our reporting also needs to separate time between our…