-
Correct Formula Syntax for checking for excluding blank cells
I have a RAID log sheet where I want to add a helper column to capture the aging (duration) of each issue row that has not been marked as complete (and I want to convert the formula to a column formula). This sheet is generated for each project via our control center solution so there are several rows at the top of the…
-
Any suggestions for keeping infrequently-visited sheets/reports/formulas updated with current data?
Hi everyone, I have a scenario that worked well for 3 years, but in the past 6 months or so has become unreliable: As part of a templated toolset, I have a couple of 'worksheets' (where users are visiting and making updates), a 'calc sheet' that's pulling information together from these worksheets via index formulas with…
-
Circular References and Parseable Errors with cells that contain Emojis and Text/Numbers
I'm new to Smartsheets and attempting to transfer tracker data from Google Sheets to build dashboards. I use emojis in trackers and COUNTIF formulas to summarize the data based on emoji categories. The formulas work as long as there is no other data in the cell but I get a "0" if there is any other text in the cell. Please…
-
Check box for start date and end date that fall within Today's date
Hi Community I have a check box column with a column formula to check if the Start Date and End Date are Todays Date. =IF(AND(ISDATE([Start Date]@row ), ISDATE([End Date]@row ), [Start Date]@row <= TODAY(), [End Date]@row >= TODAY()), 1, 0) This works when there is a date range, ie Start Date is 18/2/26 and End Date is…
-
Sum Collect or If Statements
I’m trying to build a formula that can achieve the following logic, but I’m struggling to get it working correctly. I have a data sheet with around 100 project rows. Each row contains a Fab Start date and a Fab Finish date (along with many other fields). Some projects run across two consecutive months. In my metrics sheet,…
-
Index/Distinct formula suddenly stopped working last week
I had a formula to list all distinct values from a column in another sheet. I was using this formula and it was working fine until last week. What could be wrong? =IFERROR(INDEX(DISTINCT({Liste de matériel Plage 1}); Séquence@row ); "") When I remove IFERROR, I get #INVALID DATA TYPE
-
Converting Drop-down Date to a Date formatted value
First off, I am using Control Center to build out projects that are dependent on a central worksheet, "Weeks of…" as a drop-down source list. The "Weeks of…" is contained in a Portfolio folder in the Admin workspace. Secondly, I have a worksheet, "Weeks" that contains a number of dates, DATE. In this worksheet I have an…
-
Is it possible to subtract a duration from a date which drives the start date
What I am trying to do is Helper - Duration = Actual start date. So, for example: 14Apr2026 - 22d = Actual start date. Is this possible? Note: I need to keep dependencies turned on.
-
Having trouble comparing two cells
I'm having some issue with comparing two cells in my Smartsheet. I want the box to be checked if the cells match, and if not, the value of the one on the left should turn red. I have 8 sheets for 8 different budget accounts that are all based on the same template. This works for every one of them except for this one. In…
-
How to determine the last two lines of a sheet when row is checked
I'm trying to find and number in the Rank column the last two rows that have a status column with a check in the checkbox. I am using the following formula but am getting a #INVALID OPERATION error. =IF(AND(Status@row = 1, [Row #]@row >= LARGE(COLLECT([Row #]:[Row #], Status:Status, 1), 2)), 1, 0) The properties for each…