-
Autogenerate IDs built off of other IDs?
Hello! I'm trying to auto-generate IDs that build off of another ID. The formula I'm currently using is: =[Initial ID]@row + "-R" + COUNTIF([Initial ID]:[Initial ID], [Initial ID]@row) I would ideally have it set to: =[Initial ID]@row + "-R" + COUNTIF([Initial ID]1:[Initial ID]@row, [Initial ID]@row) But that's not allowed…
-
Index/Collect/Lookup not working
I've put together a function that pulls an autonumber value from a cross-reference. The problem is that the formula does not evaluate on all records. In the first snippet below the Row Record field does not return a value for all lookup values in the Base PO field even though I have confirmed that all the lookup values…
-
adding dates based on quarter
Hiya, I am trying to create a formula to add specific dates based on the selected Quarter. e.g., I select 'Y3Q1' in the Quarter column and it outputs '9/1/2023' in the Date column. I thought I could do this with IF statements but its a no-go: IF([Column13]@row= "Y1Q1 " , " 9/1/21 " , IF ( [Column13]@row= " Y1Q2 " , "…
-
Health Status Formula
Hi! Currently, I have a formula in place to indicate R,Y,G Health based on the Start/Due Date. Is it possible to add an =OR function so that if "Status" column is switched to complete the Health will indicate green. Sorry .. very new to this.
-
If price above/below number of cents...
I'm new to SmartSheet and am struggling to create a formula for the following: If number ends in 0-29, round DOWN to nearest 0.95, (ex. FLOOR(A1,1)-0.05). Otherwise, if number ends in 30-99 cents, round UP to nearest 0.95 (ex. CEILING(A1,1)-0.05). ie: $59.29 rounds to $58.95. $59.30 rounds to $59.95 Can anyone help me put…
-
Slot scheduling conflict within a date range
Hello Smartsheet Masters, I am making a scheduler sheet for equipment slots. The sheet contains these columns: Conflict (Checkbox), Row (Dropdown list), Slot (Dropdown list, multi-select enabled), Start Date, End Date. I would like the Conflict cell to be automatically flagged if any one or more slots have already been…
-
How Find and Replace Contacts - Need change users from personal email to work email
Is there away to easily find and replace contacts in column? All of our users are currently entered as a contact with their name and personal email and we need to change all of the users to their new work contact. For example I need to update an entire column worth of data and change Mike Stone xxx@gmail.com to Mike Stone…
-
COUNTIF + MATCH? between multiple sheets
Hello, I'm needing help with another formula. This is my objective: Match the [Location ID#] between Sheets A & B. Sheet "A" contains one row per location. Sheet "B" contains multiple rows per location. Then, count the number of "Yes" values in Sheet B for each location and return the count total to Sheet A for each…
-
How do I amend this formula
What should this formula look like if I also want it to ignore any tasks that do not have an end date? =IF(AND([End Date]8 < TODAY(), NOT(Status8 = "Complete")), 1, 0) Its template formula from a project template set that surfaces at risk tasks to the roll up dashboard. IE those where the end date is i the past and the…
-
Conditional Color
Hi all! How do I create a formula if the result is between 1.00-1.10 it will turn green and if all others - it will turn red? It will have to be added to this current formula because the cell uses it to calculate the average. =([1st Patient H/L]@row + [2nd Patient H/L]@row + [3rd Patient H/L]@row) / 3 Thank you!!