-
Countifs OR Formula Issues
I am doing a countifs formula, and am unsure how to include an OR formula. In the Summary Program column located on another sheet, I want it to count the number of both Bi-Annual and EMEA cells. for rows which Summary Season is S21 This is the formula I have used that resulted in an Invalid Operation error.…
-
Can character limits be added to Text fields?
I have a free text comments field in a sheet, but I want to limit the number of characters people can enter into it. This is to try to keep comments brief and to the point. Is it possible? Thanks
-
Find the unique dates from a sheet AND display them from oldest date
From a previous post I can find all of the unique dates from a sheet within the last 45 days. My problem is that the dates are not listed from oldest to newest, so when I add this information to a dashboard it does not display correctly. Here is the formula I'm using: =IFERROR(INDEX(DISTINCT(COLLECT({Column 1}, {Column 1},…
-
Is there a way to prevent cell value assigned by formula from changing after sorting?
We need to assign a number and letter combination in each row of a sheet. Some rows are considered to be "in a series" and will need the same number but a different letter, i.e. 40001A, 40001B, and 40001C. We have a formula that will assign the letter based on the count of the number, i.e. the first time 40001 is entered…
-
Countifs and Is not blank
I am trying to create a formula where it counts the number of rows when an invoice has not been approved. This is what I have and instead of changing the name for each person, I want to create one where it counts when the Approver column is not blank. =COUNTIFS({Approver4}, "Eric Delie", {Hold}, "")
-
How to return a value from a reference sheet based on two columns in target sheet
I am trying to use two columns in a target sheet to return a value from a reference sheet. My formula is =JOIN(COLLECT({Designated Recipient Focus Area}, {Designated Recipient Focus Area 1}, @cell = [Focus Area]@row, {Designated Recipient Service Line}, @cell = [Service Line]@row)) For the first Designated Recipient Focus…
-
Linking a cross referenced date column to another date column in the same sheet
Hi team - I have a sheet that I'd like to link a cell within the same sheet. The MS Due Date column is a linked to another date field on another sheet. I'd like that field to be linked to the Target Finish column for specific rows (and don't want to just copy/paste info from MS Due Date to Target Finish). Is that possible?
-
Index, collect, Max
Need help selecting the most recent two notes into a single field based on the current date. Data: Grid 1 Desired output example: one Site with the last repair two notes in the same field. Current notes field formula: ? Grid 2 -MAX(COLLECT AND INDEX(MATCH variations with no success. Index(collect(IF(D6 ="","",TEXT(Date,"d…
-
if the answer is zero
Whats the correct way to do this? =if(SUMIFS({Service Projects Tracking Master Log Range 1}, {Service Projects Tracking Master Log Range 2}, AND(@cell >= DATE(2021, 8, 1), @cell <= DATE(2021, 8, 30)),0,75,000)
-
Formula not working Parent Row
Hi All, The formula we have implemented on our programs to automate the colour dots does not work on the Parent row due the summary of the days being calculated from the first start to the last finish - thus the duration is technically "incorrect". I understand however that is just how smartsheet has set up the program to…