-
Date Calculation not working correctly
In my Operating Calendar sheet, I have a Due column (date). This due date column should be calculating based on a formula that adds the "Days From Pricing Group OR 1st Departure Date" column to the "Pricing Group Date" Column (=[Pricing Group Date]@row + [Days From Pricing Group OR 1st Departure Date]@row). Both the "Days…
-
IF formula help please
I have this formula that is working perfectly, it returns the value in number of days between Date submitted to Todays date OR Date submitted to Date Comments Log Received =IFERROR(IF([Comments Log Received]@row = "", NETDAYS([Date Submitted to SJTA]@row, TODAY()), NETDAYS([Date Submitted to SJTA]@row, [Comments Log…
-
Sumif formula on multiple drop down cell
Hi, I need to add a sumif on a multiple drop cell so depending on what options i choose it will sum all options selected in drop down. Is that possible?
-
Checkbox in source sheet pulls data into destination sheet
I know reports are the more favorable way to go with this but that won't work for what I need. Use Case: Departments have a tracking sheet with high-level detail. Other departments want to track various level of detail in their own sheet. Only some of that data needs to make it back to the roll up sheet for executive…
-
Using IF and unchange the cell value if it happens to be false.
I have this worksheet Column B is a drop list of variables - "Completed", "In Progress", "Enrolled", etc. Column C is Date Completed (meaning if the learner completed the course, we stamped the date when this learner completed the course. So when I input in the date, I would like. Column B to change to Completed form…
-
Join Collect
I'm using a formula which is returning the data I need but it is duplicating same results - is there a way to get just one of each result? =JOIN(COLLECT({status}, {ref}, [Order ref]@row), ", ") returned data: I just need it to say Pack and Dispatch, Assembly - is this possible? Thanks
-
Having an issue with COUNTIFS with multiple criteria and referencing another sheet.
So, I am having an issue getting COUNTIFS to properly calculate. What I am trying to do is have it count rows with a status of TARP and a co-pay of $0.00. This is the current formula. =COUNTIFS({PARENT ACTIVE 2a Range 1}, CONTAINS("TARP", @cell), {Parent Co Pay Amount}, CONTAINS("$0.00", {Parent Co Pay Amount})) Now…
-
1st - 5th highest value that meets criteria - cross-sheet referencing
I have a Smartsheet entitled 'MASTER List' with the following information (see Image 1 below). The columns 2021, 2022 and 2023 use cross-reference formulas to count the number of times these courses appear in other sheets for the given years. All courses are categorised into one of seven categories: Health & Safety,…
-
Looking at 2 sheets for data that does not exist in 1 sheet and providing a blank cell
I have a sheet that contains a lot of data that is an export from a separate data source (sheet 1). The separate data source also produces financials for certain periods, e.g. Q1 2023 and is exported to a separate sheet (sheet 2). Where there is no data for this period the report does not provide any data as it does not…
-
Formula for average between 2 dates
Hi all, Probably a very stupid question, but is there a formula that presents the average date between 2 dates. For example, the average date between Monday 4th Septmeber and Wednesday 6th September should be '5th September'