-
Calculate cumulative duration of time spent in each status where status changes are bidirectional
Hi, I have a problem I am trying to solve in a sheet that contains hundreds of individual requests. Each request starts with Status = 'New' and finishes at Status = "Complete" in between there are 3 review statuses, "Review Department A", "Review Department B", "Review Department C" depending on the department the review…
-
IF, Index, Match formula
Hi, I am trying to see if there is a formula that will look at the Cells in row 1, and if matched to the any of the weeks in column Week Of, will return the corresponding deliverable value in Column1. Attached is the clip of my smartsheet. Does anyone know if there is a formula that can do this? I have tried using IF,…
-
Valid sheet cell value becomes #INCORRECT ARGUMENT SET when aggregated into a report
Hi, I have a formula which displays the correct value at the sheet level =IF([Date Answered]@row = "", "", (YEAR([Date Answered]@row))) example output = 2021 or if there isn't a date present the cell would be blank. However, when I aggregate the sheet rows into a report the cell values show as #INCORRECT ARGUMENT SET Can…
-
Which formulae is it to compare 3 values and give me 1 of 3 different text options in a cell?
I have created a form that populates a sheet and the form asks multiple questions and then the sheet uses the answers to create 3 costs, so far very simple. However I want another cell to automatically display a result based on the 3 costs provided. I'm a contract manager and I will have 3 options I wish to display they…
-
Automatically Copy Column Entries into Another Sheet
I have two separate audit sheets, and wish to combine certain columns into one summary sheet. To be more specific, I only want the PO number and overall pass fail from each large data sheet to be automatically copied into a 3rd sheet so I can compare results of the PO number's 2 test results. In theory, the resulting…
-
COUNTIF and Today Formula - How to get this right?
We would like to count how many cases fall within the previous 8 days not including today. We currently have the formula setup as =COUNTIF({Range}, this is counting the current day all the way back to 7 days ago. Please let me know what the appropriate syntax would be. Thank you!
-
Joining cells and adding an IF condition
Hello! I am joining cells by using the + : =[Select 1535 Levante Equipment]@row + [Select 1551 Brescia Equipment]@row + [Select 1552 Brescia Equipment]@row + [Select 6565 Red Road A Equipment]@row + [Select 6565 Red Road B Equipment]@row + [Select 6565 Red Road C Equipment]@row + [Select 6565 Red Road D Equipment]@row +…
-
Date Formula Help!
Hi All, I am trying to find a simple formula that allows me to add months onto a date to get an automated due date. So date completed column plus whatever figure i put in months column ( up to 12 months) and then this to populate in the Due date column.
-
The dreaded #INVALID DATA TYPE error
I created what I thought was a simple nested IF statement, but got hit with an #INVALID DATA TYPE error. Both columns have the Column Type = Text/Number All "State" fields are populated with valid 2 character US state abbreviations. What am I missing? Formula: =IF(OR(State@row, "AL", State@row, "KS", State@row, "MI",…
-
copy text from one cell to another in one sheet
Hi, solution seems to be simple ;-) I want to show text that I choose from a dropdown field in another cell in the same sheet. [column one] [column two] [column three] before choosing [blank] [options 1-5] [options 6-10] after choosing [ =text no. 7 ] [options 1-5] [chosen option 7] what kind of fomula do I have to enter…