-
Index and Match formula issue
Hi All, My colleague and I are trying to find the first occurrence of non-zero in a table and then return the corresponding month. Example table The formula that we are trying to work with is below, but whatever we try we keep getting an error of #UNPARSEABLE =IFERROR(INDEX(January31:December31, MATCH(True,…
-
Date Function
Hello, I need to record prior dates in the following 3 date fields which is based on the date column. Can someone kindly share how to write a function for this and explain it? It is a possibility that I may have to included a 90 days prior field and I want to make sure I understand the formula enough to know where changes…
-
How to select column name when typing a formula?
I often use formulas like COUNTIF, SUMIFS etc. where I want to select an entire column as a range, e.g. =COUNTIF([Date of Issuance]:[Date of Issuance], [Date of Issuance]@row) If I was doing this in excel, I would type "=COUNTIF(" and then click on the column heading to populate the range part of the formula. This doesnt…
-
Am I missing something - adding formulas in sheet summary?
When I attempt to set a formula in sheet summary the wizard give one option of ‘Refernece another sheet’ and not the sheet you are in. When I click click on the column header in the sheet (i'm in) to set the range this is what I get this: This is different to adding a formula to a cell in a (metrics) sheet where you can…
-
If is blank and not blank formula
Hi i have two columns with Date as column property “Next PoC Date” and “ Target end date” and i need to have an additional column that show me the following: If Next PoC Date is in blank show me “Target end date” If Next Poc Date is not blank, show me “Next PoC date” All these in the same column. (This column will be the…
-
Complex health status with both gray and blue options
Hi, I am trying to generate a health status formula for my sheet with the following conditions. Ideally I would like to have green, red, yellow, grey, and blue health options but I am not sure if that is possible. If so, my conditions are: Blue: Status column is "Completed" (regardless of start or finish dates) Green:…
-
Two Levels up PARENT formula
HI THERE! Would like to ask if there is a formula that allow us to access the parent row but always the level 2 hierarchy. Normally, PARENT() solves the inmmediate parent only. Much appreciated.
-
PTO/Sick Accrual Calculations
I started a new job and a bit overwhelmed. I am new to Smartsheet and a basic user with Excel. I need help with PTO/Sick policy calculations within Smartsheet. I do not know where to begin. Any help is appreciated!
-
Index Match and Filter?
Hello! I've successfully pulled data from one sheet to another via Index/Match while using an 'IF' statement within the same sheet to filter results using the following formula. =IFERROR(IF([Budget Action Requested]@row = "Initial", (INDEX({Date Specs Completed}, MATCH([Unique ID]@row, {Unique ID}, 0))), "N/A"), "") But…
-
Formula to calculate fiscal quarter when quarter starts on February
Hello, I'm very new to SmartSheet. I found a discussion from 2020 about doing a calculation for fiscal quarter and including year, but I cannot quite get it right. Our FY starts in February and ends in January of the next year. I'm trying to show the quarter and year as Q1FY24. Here's is the formula based on one created by…
