-
Trying to Create a Dynamic Formula and need some guidance
Hi, Working on an advanced Dynamic Formula where selections on the sheet drive the formula results where we have several different criteria driving different selections. [Type] a selection would be either "A" or "B" AND [Milestone] selection is driving a Reference column to indicate the YEAR (ex. Milestone 1 = FY23,…
-
How to remove last character in a concatenation
I have a series of columns concatenated, separated by _ (so it looks like "A_B_C_D_E_...") Some all columns after E are optional. The end result means some concatenations end in "_" I think a helper column might be needed, in which case the formula would be something like that solves for 'if row ends in "_", remove "_" -…
-
Formula to Count The First Or Second Time Something Has Appeared In A Sheet
Hello, We're currently using a sign up sheet for people to attend some additional trainings but we're limited to 2 people per training and we don't want everyone to have access to the training information to change it, so we created a list of all trainings for people to look at. Each training has a unique ID attached to it…
-
Use returned value from one index match inside another index match formula
I have searched and have not found someone with this same question, but I'm sure it's probably there, but stated differently. Index/Match 1: Unique ID returns "X" value from Sheet 1 and populates cell in Sheet 2 =INDEX({Sheet1 reference value to return}, MATCH(X@row, {Range from Sheet1}, 0)) This one works with no issues,…
-
Count Number of Months
Hello, I am looking to count the number of months remaining in a calendar year without having to change my formula monthly, simply put if the date is 9/15/23 I'd like the output to be "4", if 12/20/23 = "1" etc. @Paul Newcome
-
Auto Health Formula
I am looking for a formula that will auto populate the symbols in my "Flag" column. If "Status" is equal to "Complete" - Gray If "Requested Due Date" is prior to "Requested Due Date" AND "Status" is not equal to "Complete" - Green If "Requested Due Date" is equal to "Requested Due Date" AND "Status" is not equal to…
-
Formula - Current Week/Last Week
I have been using a formula in one column to capture the current week and last week based upon the created column. I am not seeing my mistake or what I am missing, hoping for some guidance please. However, last week I noticed that the formula is capturing a year ago as current and last. The formula I have been using:…
-
Subtracting with a Formula in Cell
Hi there - I am trying to do simple subtraction within my sheet. =Budget@row - [Estimated OSC]@row - [Expenses]@row This formula normally functions correctly; however, I recently changed the Budget column to an "IF" formula, and now I am receiving an #INVALID OPERATION error. I am not sure how to structure the subtraction…
-
Event Management and Attendee list help
We are hoping to use Smartsheets for event management, with a master sheet having columns such as 'Name, Role, Company' and additional columns being the Event name (drop down list with dates) and 'I would like to attend this event' (checkbox) We would then create a form for each event, using the basic columns and then…
-
IF, AND, CHILDREN formula
I have a project checklist that has parent rows where I am trying to show an overall status trend for the children rows that fall under each parent. I have a current formula set up with the use of a helper column that says: =IF(Scoring@row = 0, "Sideways", IF(Scoring@row < 2, "Down", IF(Scoring@row < 3, "Angle Down",…
