-
Freezing Dates
I am looking to have a blank cell populate a date in a column using an if statement to check for specific values in another column. With excel I would normally use a circular reference to check if(and()) the cell was blank and that the values I want are in another column, and then populate a today(). With Smartsheet it…
-
VLOOKUP Output Doesn't Match Expected Value
Hey Everyone, I've been having a hard time with some missing features or current limitations within SmartSheet but have found the communities helpfulness in finding various work arounds very useful. I'm hoping that somebody will have an idea for the issue i'm currently running into now. Having made some helper columns…
-
Text String Help... Again!!
Hello All, feel like I am always asking these text string questions but they are so confusing!!! I have a list of retailers and I am trying to just extract the Name of the retailer between either the 1st and 3rd "-" or the 1st and 2nd "-". The problem is with how some of these are formatted like the first one and second…
-
CountIfs Formula
I would like the below formula to look at the Associate column (external sheet) and find Ashley. If it is Ashely then I want it to count if there is an issue logged in a four column range (same external sheet). It is comparing the issue in the range to the cell in my primary sheet. =COUNTIFS({Associate}, "Ashley", {Issue…
-
SUM Rows in Column A that meet multiple criteria including distinct values in Column B
I am trying to use a formula to: Referencing other sheet(s)… Sum story points by PE (Person) if: The Parent PCR (Identifier) is Unique The PCR Status is one of 5 options The PE assigned is an exact match (ie "Derrick Franklin") Here are a few that I feel are close, but not giving me the exact number's I'm looking for: This…
-
Automation help or formula for connecting rows
AW Placement request form below takes in all of the school placement requests via a form. Once the column AW REQUEST STATUS is 'approved by leader' it is automatically moved to Students/Instructors - AWI The ANCHOR ID transfers over from AW placement request sheet to students\instructors - AWI - when approved by leader…
-
IF AND OR formula
I have been trying for way too long to get this formula working correctly - time to ask the pros. I would like the "At Risk" column to flag if "Due Date" has passed and if "Status" is anything other than "Completed" or "% Compete" is anything other than "100%" I've tried many different ways to get this to work without…
-
Formatting in Formula Cells
I have two date cells that I have formatted as long dates (May 30, 2018) titled Contract_Begin and Contract_End. I want the formula cell to read contract begin date through contract end date (for ex: May 30, 2024 through August 10, 2024). However, the cell formats to 5/30/24 through 8/10/24. I posted the formula I am using…
-
Which Index formula should I use to cross reference multiple columns?
I have a sheet that includes 5-6 columns of information that I want to link to a new sheet that people will work from. The information in these columns will be updated in the original sheet so I would like the cells to be linked instead of copied. In the original sheet, the information repeats so I only want the references…
-
#BLOCKED Error with COUNTIFS when Range columns are populated via equations
Hi, I am trying to calculate the amount of "Used Inventory" by counting the number of times "Block Date" appears between "DATE 1" and "DATE 9" but am getting the #BLOCKED error. Columns "DATE 1" through "DATE 9" are populated by equations based on "CheckIn Date" and "CheckOut Date." Below, you can see the columns in use…