-
OFFSET() function
I've not seen any requests for a Smartsheet's equivalent to Excel's OFFSET() function. For how universal the INDEX(MATCH()) solution seems to be, it strikes me as a notable oversight that there isn't any function to achieve a similar effect for selecting a range of cells offset by a column index value. It would greatly…
-
Best practice to apply formula to columns
Hey y'all. I often catch myself stuck on applying a formula to a whole column. Is there a best practice or more effective way to apply formulas?
-
COUNTIFS between dates & criteria.
Greetings Smartsheet Community, I am attempting to cross reference another sheet and Count all the Completed tasks within a certain time frame. So far I am successful counting the dates in the range using the following formula: =COUNTIFS({Range 1}, <=DATE(2018, 4, 31), {Range 1}, >=DATE(2018, 4, 1)) Next I'd like to add…
-
Locating the parent of a parent
I am trying to take the value from the primary column of a level 0 parent and insert it into another column in a level 2 child. =PARENT([Task Name]@row) works fine when there is only level 0 and level 1. Something like parent of a parent is what I have imagined would work, but =parent(PARENT([Task Name]@row)) is…
-
Find and Replace in Formulas
Hello, I use a TON of formulas that involve employee names. When creating sheets for new employees I need to paste the names into each formula. It takes FOREVER because I cannot find and replace within the formulas. Am I missing something? Also, how do I stop this from happening? I can't make a column "number only" so I…
-
Formula to add 1 day to date
I'm having trouble with a formula to add one day to a date based on a condition. I'm sure it is something terribly simple that I'm just missing right now. My formula worked previously when I had AND in the formula with two conditions, but when I only have one condition it adds a 1 to the end of the date rather than adding…
-
COUNTIF Formula for Checkbox Column Not Working in Sheet Summary
I'm trying to create a formula in the Sheet Summary that shows the number of checked items in a Checkbox column named Pending, out of the total number of rows that have either a checked or unchecked box (i.e., excluding blanks). =COUNTIF([Pending]:[Pending], 1) + "/" + (COUNTIF([Pending]:[Pending], 1) +…
-
Multiple IF Statements Using "ISDATE"
I have an existing formula that enters text based on two criteria: if a date exists in a column, and if that item was closed prior to the anticipated delivery date. The current formula is: =IF(ISDATE([Closed Date]1), IF([Delivery Date]1 > [Closed Date]1, "Complete", "Fail")) I'd like to add an additional criterion that…
-
VALUE RETURNING AS BLANK
I have a data sheet with several items listed and their referenced codes. On another sheet I have a form whereby my engineers can select from a dropdown list the item and whereby, based on their selection, the code should publish in an adjacent cell. The formula I use works for the first item on the dropdown list but the…
-
Limit to Multiple Formulas-All IF statements.
Can you help me write a formula that has 10 different conditions and references multiple columns on the same smartsheet? I've utized the AI tool to assist me but when I get to the 7th formula, it cannot compute.