-
Formula to add checkmarks from one sheet to another
I am trying to have two sheets communicate with each other and I've tried using the Index Match formula with no success. Sheet 1 is an attendance check-in sheet based on a form submission. Sheet 2 is a high-level tracking form that needs to be organized in one row per person (whereas sheet 1 will have several entries per…
-
Tricky Formula - Need Help
I need help in figuring out a more complex formula. Below is a screenshot of my sheet. What I'm hoping to devise is a formula to go into the yellow highlighted cells (a unique formula for each column, I expect) that essentially tells me: Based on "Deadline@row" and "Remaining Hours@row", how many hours will have to be…
-
Return Value if Between Dates
Hello! I have a [DATE SUBMITTED] column and need to create a reference column that returns the fiscal year that date falls in. Something like this - =IF [DATE SUBMITTED] > 10/01/17 AND [DATE SUBMITTED] < 09/30/18 RETURN "FY18" and =IF [DATE SUBMITTED] > 10/01/18 AND [DATE SUBMITTED] < 09/30/19 RETURN "FY19" I'm not sure if…
-
Need to know what's causing the incorrect value shown after formula is created
I'm trying to count all rows that are tagged as "4. Billing Adjustments" within the current year from "Date Submitted" column also tagged as "Credit" from "Type of Change Requested" column but not getting the correct result =COUNTIFS([Attrition Stage**]:[Attrition Stage**], "4. Billing Adjustments", [Type of Change…
-
Way to find and change font on a specific set of rows?
I am looking for creative ideas here. At the beginning of our fiscal year, we copy just a specific set of rows from our enterprise project list to the IS project list. We change the font color on just this set in the receiving sheet so we have a visual cue when we look at the full list. Now, after the fact, we want to find…
-
Formula for counting dates in a month
I need to count how many jobs there were per month on another sheet, any tips on how to make the "COUNTIF" or "DATE" My current formula for the month of January that comes up as "Unparseable" =COUNTIFS({Date}, AND((>=DATE(2024,1,1), <=DATE(2024,1,31)))) {Date} is the column from the other sheet Help would be appreciated
-
How to count current week for the month?
I need a formula to calculate the week number for the month, for example 28 May 2020 is the 5th week of the month. I understand that week number calculate from w1 - w52. How can I calculate the week number for the month? So basically, the answer will be between number 1 - 5. Thanks.
-
Pulling Data from another sheet
I have two sheets with a few hundred rows filled with site information. The common data between each table is the site number. What formula would I use in a cell if I wanted my primary smartsheet to check the other sheet for the site number on a row and return data from a different cell in that same row? All site…
-
Using variables in Hierarchy logic
Is there any way to get the parent row number of a task from another Hierarchy. Im using a function that can call get information of its predecessor task, its working but i want to get information from that same predecessor's parent row. IF(TRUE STATMENT , (INDEX([% Complete]:[% Complete], VALUE(Predecessors@row + "") -…
-
Multiple IF / AND / OR Statements Help, Please.
This is such a great community and a wealth of knowledge for a new Smartsheet user like me. I have done several formulas, but this one has me scratching my head. I have a [Target Date] column, [% Complete] column, and status column that I would like to automate with Red, Yellow, Green, and Gray status balls. The [%…