-
Convert Measure Symbol to number & Average
Hi, Since my team has selected the pain scale symbols as their preferred method of scoring work, here I am struggling to make the following formula work. =IF([Value to User]@row = "No Pain", 4, IF([Value to User]@row = "Mild", 3, IF([Value to User]@row = "Moderate", 2, IF([Value to User]@row = "Very Severe", 1, IF([Value…
-
Linking Smartsheets
Hi, first timer here... So I'm using Smartsheets to track issues that are identified on a particular system so that I can maintain a count of those issues using a single sheets. All well and good. Now, I need to (I'm thinking in a Separate sheet) track the MONTHLY issues. For example, April I tracked 10 issues, and in May…
-
Can I have one section of a formula check two different sheets?
So, here's what I'm trying to do. I have three sheets in total I'm working with. All three sheets contain a unique identifying number for easy reference. Sheet A = Master sheet which is where the formula is housed. Sheet B = Sheet with usage data for site 1. Sheet C = Sheet with usage data for site 2. Both Sheet B and…
-
Crosssheet checked box count
I have Sheet (A) I want to you as a summary sheet for Sheet (B). I want sheet (A) to count howmany checked boxes are in a specified column in sheet (B)
-
COUNTIF Formula Providing Incorrect Result
Hello! I am adding sheet summary fields to count the rows for each status. I noticed that the results I get with my COUNTIF formula are incorrect. The correct results would be: Completed - 3, In Progress - 1, Not Started - 2 (this one is correct) I tried this formula on a second sheet and still received an incorrect…
-
Nested COLLECT question
I need help with creating the formula for the [PrevHours] column (below). The expected logic for this formula would be: for a given row's ColValue, find the max date from all instances of ColValue, then find that row's Col2Value. For example: Project | Date | Hours | PrevHours XYZ | 5/1/23 | 10 | "" XYZ | 5/2/23 | 15 | 10…
-
Copy duration value to backup field and restore on specific status
I use a standard project template when beginning each project. As requirements are reviewed and tasks are not needed we mark the status N/A and zero out the duration. I'd like to be able copy the duration to a backup field and should the status change to anything other than N/A or Complete copy the backup duration back to…
-
Checkbox Formula: If Due Date has passed but completion date is blank
I have two columns: 1: A "Date Calc" field (contains a formula) that automatically displays a due date for a task based on manually entered dates from a prior field. This is a date column. 2: A Date Complete field that is manually entered. This is a date column. I have added a third column that will automatically check a…
-
Sum of children if it has no descendants
I am attempting to create a single cell formula to capture the total value within a budget sheet, however =SUM(DESCENDANTS()) is capturing data multiple times and doubling costs due to children rows being routinely being summed (=SUM(CHILDREN)) by staff for reconciliation purposes. The sheet layout has a parent row with…
-
Index Collect Max | Get the most recent item based on Created Date
Im using the formula below, but getting an error #INCORRECT ARGUMENT SET =INDEX(COLLECT([Current Note]1:[Current Note]7, Auditor1:Auditor7, Auditor@row, [Created Date]1:[Created Date]7, MAX([Created Date]1:[Created Date]7)))
