-
Index and match doesn't always work
Hi, I was wondering if anyone had come across a technical issue with index and match or if it's something I am doing wrong (the formula is definitely correct). I have a critically important index and match on a large number of sheets but it has stopped working. Replacing it with vlookups work so I have a fix but I'd rather…
-
Sum Hours in a column by row name
Formula>> =SUMIF([Assigned To]:[Assigned To], [Assigned To]@row, [Hours Testing]:[Hours Testing]) Formula works if only on a single row. eg will produce the sum 3 next to "Armida" When i copy the formula it reads Circular Reference. *Goal is NOT to type name in formula "Armida". I'd like it to reference the row Assigned To…
-
Sheet Summary field suddenly started to append date with time of day
A Sheet Summary field that concatenates the next scheduled task name and due date suddenly began this week to append the date (MM/DD/YY) with the time of day (HH:MM). No changes were made to formula. Example: Formula: =IFERROR(INDEX(COLLECT([Task Name]:[Task Name], Actual:Actual, ISBLANK(@cell), Status:Status, "In…
-
How to Identify Skipped Number
I have a column containing document numbers that looks somethin like this: s-001 s-002 s-004 Does anyone know of a formulas that I could put in the sheet summary to return the first skipped number (in this case, s-003)? I know how to do this with a "helper column", but I'd like to avoid that if possible.
-
How do I create a ONE to MANY mapping cross-sheet formula/mapping?
I have a sheet called a "Catchall" which is the FULL list of projects being worked on in the company, and for every project on the list, there is a related OKR, start date, end date, health score etc I need to be able to pull all related projects that share a COMMON objective into a different sheet. The goal being to be…
-
Help with a date formula.
I would like to enter in a date into the end date column (its currently enabled as a date column) however the formula listed below returns an error of #DATE EXPECTED. The false value "-" shows up but the date does not. Any help on this would be greatly appreciated. Here is my logic written out. if rotation # column = 1…
-
Tracking age in hours
Hello, We are using Smartsheet as a ticket queue (users submit tickets via a Form) and I'm wondering if there is a way to track age in hours as well as hours between updates. For example, if we want to show how many hours it took to assign the ticket and how many hours it spent in a particular status and things like that.…
-
Counting Distinct Contacts in a Contact List Column
Hello. I have a tracker and in it I am using a Contact List for "Assigned To" and I allow for that to contain multiple contacts. I am trying to count the number of distinct individuals in the column to come up with a total number of contributors to the effort we're tracking. I used: =COUNT(DISTINCT(Leads:Leads)) The result…
-
Need assistance with invalid operation result
For the below once I have a date in [In Progress Date] or [Actual Completion Date] it sets #Invalid Operation when I expect a date result. =IF([Actual Completion Date]@row = 0, IF([In Progress Date]@row <> 0, [In Progress Date]@row + ([Estimated Days to Completion]@row * [Resource Time Percentage]@row), "TBD"), [Actual…
-
INDEX, MATCH, COLLECT, CONTAINS ... oh my.
I have two worksheets. Worksheet A ("Verified Content Report") has about 100 columns. On each row are details about a specific asset. One of those details is in a column called "Owners," which contains one or more user IDs (comma-separated) for each individual "owner" of that asset. Worksheet B has 2 columns: user IDs and…