-
Hide Divide by Zero Error
Hello, I am having issues with creating the correct formula that calculates the average of the children rows while hiding the divide by zero errors for columns that do not have any data. Could someone please help? I tried the following =IFERROR(AVG(CHILDREN([Column]@row],""))) Thanks so much!
-
Convert text date, to date 2018-04-18
I have a primary field that contains date information and I need to convert it to a date, so that I can compare it against another date field. The format of the text date is 2018-04-18, and I need to convert it to a date, and cannot find a formula to do that. Any help? thanks d
-
Median formula showing highest value from range.
Hi everyone, I have two sheets, a master sheet with cost data and a metric sheet with formulas to get a summary. In metric sheet, I need median value of certain items. I have used MEDIAN & COLLECT. Problem: When I updated the formula, I got the correct median value. But after sometime, the metric sheet gets refreshed…
-
How can I convert YYYYMMDD to a SmartSheet date value?
I have about 2500 date values, imported from an Excel spreadsheet, in the YYYYMMDD numerical format. I would like to convert them to Smartsheet date values. Any suggestions?
-
The correct VLOOPUP formula?
Hello I am needing a formula that will pull an employee level and assign it to the alias listed. I have used differed variations of VLOOKUP and nothing seems to work. Breakdown: Sheet #1 will contain a list of an employee alias's, sometimes the alias will be listed multiple times. In the column "Level (Formula)" on sheet…
-
30 Weeks Out - Formula Question
Hello, I have a project that has certain tasks that need to start a specific number of weeks ahead of the project end date. For example, I have 30 weeks out tasks, 29 weeks, out 20 weeks out, etc. On my project sheet, the END DATE is December 1, 2021. I need a formula that would tell me what the START DATE is for exactly…
-
VLOOKUP using flag or checkbox
How do I use VLOOKUP with Flag as the search value? I tried the following: =VLOOKUP("Checked", {Range 2}, 3, 0) =VLOOKUP("checked", {Range 2}, 3, 0) =VLOOKUP("true", {Range 2}, 3, 0) =VLOOKUP(true, {Range 2}, 3, 0) =VLOOKUP(1, {Range 2}, 3, 0) All of the returning #NO MATCH even if there is a row that has a Flag checked.…
-
Unable to edit column formulas
I'm the owner of numerous sheets and have been excited playing around with the recent addition of column formulas. However it seems like once I have created one I am unable to edit it, even as the owner of the sheet. It just gives me a little grey message saying "I have to be owner or admin of the sheet to edit". As a work…
-
VLOOKUP or INDEX
Dear all, I'm trying to use smartsheet as a Resource Management tool, unfortunatelly we haven't get 10,000ft yet. so what I'm doing is to assign the resources that we need in each project: Project Manager and Allocation % Business Analyst and Allocation % Tech Leader and Allocation % A person can be in different roles…
-
Index Collect + Max Date
Hi there, I'm having another head banging moment. I have a sheet where we track all project comments. There are 3 different types of comments (Program, Project and financial) I am trying to return a comment, where Comment Type = any of the above, and show me the Most Recent Comment of that type. I came up with the formula…