-
JOIN COLLECT with two criteria
I have a sheet full of data from another source and trying to pull some of its contents into my main sheet. The data sheet has a row for each speaker associated with a session and their role. So the sheet may have several rows with the same session title if there are multiple speakers. The destination sheet has one row for…
-
Question on usage of MAX / COLLECT in a formula
I'm trying to create a column to have the maximum 'Amount' for an 'Opportunity' in a given 'week': I have three columns: Week, Opportunity, Amount 1,ACME,100 1,ACME,200 1, NEWCO, 100 1,NEWCO,50 2,ACME,200 2,ACME,300 2, NEWCO, 40 2,NEWCO,30 And trying to create a column to capture the MAX for that Opportunity in a given…
-
Health Formula
Hello, I'm trying to create a health formula based on the below conditions, but I keep getting an #UNPARSABLE error. Hopefully someone in the community can assist. RED: If the Expected Project Go Live Date is less than 30 days from today AND the Project Phase is not "Go-Live" or "Complete" YELLOW: If the Expected Project…
-
Countifs: Help with syntax in Countifs Formula with multiple conditions
Hello All, I am trying to return a count when 2 conditions are present from a referenced smartsheet. This is the formula I created according to the guides I can find, but smartsheet says it is unparsable. Can someone let me know what the error is? =COUNTIFS({Range 3}, "Current", {Range 2}, "Critical")
-
Formula to add criteria from multiple columns from another sheet
Hi, I need to calculate how many times "Change" appears in another sheet that has the same Project ID - I want to calculate how many changes a specific project had. I have 2 sheets: Project Plan and Craid log Project Plan has 2 columns - "Task Name" with the word "Changes" in the line and "Description" where I need the…
-
Check 30 schedules in large portfolio for correct progress updating
I need some ideas how to track which schedules in a large portfolio of 30 projects/schedules have not been updated to Today's date. Each of our project managers are supposed to update their schedules every Thursday. Ideally, I want a report or formula or flag or something that tells me which schedules are updated correctly…
-
Prefilling forms
I am prefilling a form designed using worksheet "B" with data from worksheet "A" using the Substitute function. Worksheet "B" is a test defect tracker, worksheet "A" is a test script tracker. If a test script fails. I want to take several fields from the test script tracker, prepopulate them on a form developed using the…
-
SUMIFS with multiple criteria and OR function with multiple cross sheet references?
The Situation: (for context, otherwise, skip to "The Problem") Our PMs manage their projects on individual schedules, they assign the "scope" of a task, and they adjust the start and end dates for the various tasks, as well as the hours estimated to complete the tasks, and the amount of people assigned to complete the…
-
IF/AND/ISBLANK
I have a Master sheet with 2 columns (A column with account numbers and B column with some blank cells and some cells with the letter Y ). I also have my separate review sheet with column C with account numbers. I want to reference the Master sheet to determine if B column has a blank cell and if so does the account number…
-
Date formula working for half of rows
I have a columns for expiration date, 6 months prior to exp date, and 3 months prior to exp date. Formula for 6 months prior is: =IFERROR(IFERROR(DATE(YEAR([Expiration Date]@row), MONTH([Expiration Date]@row) - 6, DAY([Expiration Date]@row)), DATE(YEAR([Expiration Date]@row) - 1, MONTH([Expiration Date]@row) + 6,…
