-
Latest Date Formula
Hi I want to find the latest date a review was done on a file number. Multiple reviews could have been done on the same file so, I only want the latest date. I have used: =INDEX(MAX({01.SHEET: File Review Range 1}, MATCH([Matter Number]@row, {01.SHEET: File Review Range 2})), 0) as I am referencing the File Review sheet…
-
COUNTIF(S) to sum number of issues completed in a given range
Looking to create a sheet summary and need a formula that will return the number of items with a creation date between 05/01/2022 - 05/31/2022 that also have the build status marked as "Complete" or "Delivered". Tried these two formulas, both return as "unparseable" =COUNTIFS(MONTH([Complete Date]5), [Build Status]:[Build…
-
How to convert Full name to Last name, First and middle Name(s)?
I have a column with Full names (some with extended middle names) and want to create a column for Last Name, First and Middle name(s). For example, convert Column A "Angela Marie Walters Smith" to value in Column B of "Smith, Angela Marie Walters" What formula will return the value I'm looking for? Thank you!
-
Creating workflows with IF Statements
I have created a bunch of workflows that are having issues when they are triggered more than once. I am new to IF statements and have avoided them, hence the workflows, but I think IF statements will be a more reliable way to go? See attached workflow I created with attached sheet. As you will notice, when "3 way call…
-
Column Formula: Work On Some Rows and Not Others
Smartsheet Community: I have a Column Formula and it is Not Working Constantly. Take a look at the Empty Cells in Screenshot. I have also Added the Formula Below for Each Column Formula: Average Pain Formula: =AVG([No Pain]@row:[Extreme Pain]@row) Overall Pain Level Formula: =IF([Average Pain]@row = 0, "No Pain",…
-
Automation on self-updating cells
Hello everybody, I need to set an automatism based on a cell that is updated from a link to another sheet. The automatism is set to recognize whenever the value of that cell changes. The problem is: seems that since the modification of the cell is not done manually but it is modified by recovering the data from another…
-
Vlookup for a partial name or display name of a max collect/min collect
Hello, Can i do a partial vlookup of a name? Specifically, i have an equipment list but the equipment is varied across each project. So instead of matching the entire name, can i do a partial lookup? For example, i want to look up when our latest switchgear is going to arrive. Also, when our latest panelboard is going to…
-
Vlookup or min(collect)?
Hello, Can i do a partial vlookup of a name? Specifically, i have an equipment list but the equipment is varied across each project. So instead of matching the entire name, can i do a partial lookup? I've got the contains statement but how do i tie it location ID? =VLOOKUP({Electrical Equipments Procurement Tracker Range…
-
Hi All, I tried a nested IF AND & IF OR formula but it is showing as #UNPARSEABLE error. pls help.
Below is the formula I am using. =VALUE(IFERROR (IF(AND(Level@row = 1, (IF (OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", "31"),(IF (OR(column@row = "Test 1o1", column@row = "Test 2 of 1",column@row = "Test 3 of 1", "41"),(IF (OR(column@row = "Test 1o1", column@row = "Test 2 of…
-
Child Cell Formatting
I would like for child cells to automatically appear for each new row, and then have an index/match formula be able to populate a column in the parent row from those child rows. For example: New air freight request- 5 child rows with default values of the 5 carriers pop up (can't do a dropdown, I need to track the 5…
