Formulas and Functions
Archives for May 2020
Discussion List

First post 05/31/20,Last post 05/31/20Hi, I'm tracking an estimated Start and End date (in a Project Sheet) and an Actual Start and End date (in a tracking sheet). In both sheets the rows where the dates are entered are CHILDREN and they both have an Asset # that I'm can match…Formulas and Functions40Jeana

New to this...please help...QuestionFirst post 05/30/20,Last post 05/30/20Please help. I keep getting #unparseable error for the following formula: =IF(OR([DLExpiration Date]1 > TODAY(); [MECExpiration Date]1 > TODAY()); "NON COMPLIANT"; "COMPLIANT") I want the cell in my Status column…Formulas and Functions80Lisa Rogers

First post 05/30/20,Last post 05/30/20I am having trouble getting this formula right. I want to pull the qty of flooring scheduled to run in a given week (152) provided it requires one of 2 types of veneer. (Acacia 1220x077x1.5 or Acacia 1220x185x1.5) https://us.vcdn.net/60…Formulas and Functions80Devin Barta

How do I show health based on end date & % complete?Accepted AnswerAccepted Answer Michelle Vidal 11 views 2 comments 0 points Most recent by Michelle Vidal Formulas and FunctionsFirst post 05/29/20,Last post 05/30/20I'm trying to show health of a project based on how far out from End date + how much of the work has been completed. Green: 100% complete before or on End date Yellow 7099% and today is < 1 range of End Date <69% and date within 2 …Formulas and Functions112Michelle Vidal

Workaround for broken UTC System Created Date ColumnAccepted AnswerAccepted Answer ScionoftheNight 15 views 6 comments 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi, my problem is the same as in https://community.smartsheet.com/discussion/19461/wrongdateshowingseemstobe4hoursoffthedifferencebetweengmtandmytimezone In short, I am on GMT7 time, while Smartsheet operates under UTC. B…Formulas and Functions156ScionoftheNight

Question Carolina Rivera 9 views 0 comments 0 points Started by Carolina Rivera Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi! So I'm trying to create a stacked bar graph using two sets of data. Data set 1 has actions that are divided by months, for example, the month of May has 5 actions, while the month of June has 3 actions. Data set 2 has the same actions,…Formulas and Functions90Carolina Rivera

How do you create a Sum if equation when the criteria is based on a value in a MultiSelect ColumnQuestionAnswered  Pending Review Andrea Hill 9 views 1 comment 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20When I chose just one of the selections (I have been using "Product" no" = "or other signs) it only sums the values were there is only "Product" . the equation wont sum if there are other selections included.Formulas and Functions91Andrea Hill

Answered  Pending Review Samuel Dowdy Jr 10 views 1 comment 0 points Most recent by Sean Morgan Formulas and FunctionsFirst post 05/29/20,Last post 05/30/20Sheet > https://app.smartsheet.com/sheets/Rp5CHm6mFGcw2CG7x8rFjmfGwVpG394xffJw2x91 I really need some help placing data throughout this sheet by using formulas. I know what I want but I dont know the formulas. I want the total mont…Formulas and Functions101Samuel Dowdy Jr

Answered  Pending Review courtneyjenkins8 13 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20When "Connect Done" and "Core/Micro Done" are both checked, I want "Client Done" to automatically be checked. Can someone help me with the formula? I'm guessing its an =IF https://us.vcdn.net/6031209/uploads/…Formulas and Functions131courtneyjenkins8

Answered  Pending Review Jeana 15 views 4 comments 0 points Most recent by Jeana Formulas and FunctionsFirst post 05/29/20,Last post 05/30/20So I have a drop down column with options from 13. Each has a description similar to this: Priority 1  Nice to have 2  Needed 3  Required I would like to total the actual number that is selected in a row below these options (then I'l…Formulas and Functions154Jeana

Stacked Bar GraphQuestionAnswered  Pending Review katebrew 9 views 3 comments 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20I am trying to create a stacked bar graph using data from a sheet summary report however I have not get the report data to pull into rows as I select the field to be added to the report they are popluated as columns only.Formulas and Functions93katebrew

Answered  Pending Review Lng98881 7 views 1 comment 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hello, I have a Smartsheet where I'm trying to count distinct values in a column. Currently I've tried this but get #UNPARSEABLE. =Count(Distinct(Collect(Prj_ID:Prj_ID,Prj_ID:Prj_ID,[email protected]))) Where Prj_ID is the column where I want to…Formulas and Functions71Lng98881

Answered  Pending Review Jörg Schmidt Novum Hospitality 10 views 1 comment 0 points Most recent by Werner Gerstacker Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi, what could be the Formula in @cell "b" to insert the email address of a person whose name is written in cell "a" ?Formulas and Functions101Jörg Schmidt Novum Hospitality

Locking a line after a certain dateAccepted AnswerAccepted Answer Julie Barbeau 8 views 1 comment 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi to all, Does he have a way of using a formula to make a line lock after a certain date? For example, in our timesheet, create a formula that says: lock this row if the date in the column ''Date'' is 7 days after today's date? ThanksFormulas and Functions81Julie Barbeau

Answered  Pending Review Amy Balich 19 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20https://us.vcdn.net/6031209/uploads/DBVW41DJLDCN/snippng.png I am trying to add together the total quantity made on a certain date and time. For example, in the screenshot, I have two rows that contain 5/18/20 that occur on floorhour 5. …Formulas and Functions193Amy Balich

#Invalid Data Type HelpQuestionAnswered  Pending Review Stefano Ferretti 11 views 1 comment 0 points Most recent by Stefano Ferretti Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi All, I have this simple functions: =IF(COUNTIF(A1:D1,"apple"),"YES","NO") The formula works fine if in the range A1:D1 I have 1 cell containing "apple". If 2 or more cells contain "apple&qu…Formulas and Functions111Stefano Ferretti

Accepted Answer Benjo09 5 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi, I have a grid or sheet where the rows which document status is already "SPCapproved" need to copy or populate to another sheet called "Approved Docs". Is there a way where this ApprovedDocs sheet can automaticall…Formulas and Functions53Benjo09

JOIN formula to ignore cells in blankAccepted AnswerAccepted Answer Jean Kim 6 views 5 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi Trying to get below formula work. =JOIN([email protected]:[email protected], [email protected]:[email protected], NOT(ISBLANK(@cell), " ")) Expected results are on the lat column. Formula above is returning "Incorrect argument set" error. What a…Formulas and Functions65Jean Kim

vlookup with dateQuestionAnswered  Pending Review Archie Villa 6 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi everyone I am currently doing a vlookup to a reference column with a date format, to another sheet in a specific cell but my column is in text/number format. i got this error "invalid column value". Is there a way i could con…Formulas and Functions61Archie Villa

Answered  Pending Review bthorn 11 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hi! I'm managing a retrofit project and the end goal is to create an executive dashboard highlighting key metrics in the form of charts and/or graphs. From what I've found via searches, it seems like I need to utilize the Summary function…Formulas and Functions111bthorn

Answered  Pending Review Richard Heath 7 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/29/20,Last post 05/29/20Hello, I've developed a sheet that our Project Manager use to generate a Certificate based on a number of criteria. So the Certifcate (a dashboard) pulls the correct data from multiple sources, the user needs to simply select "YES&quo…Formulas and Functions71Richard Heath

[email protected] 19 views 6 comments 0 points Most recent by [email protected] Formulas and FunctionsFirst post 05/28/20,Last post 05/30/20So apparently smartsheet created and modified columns retain time down to at least the seconds, I wasn't willing to keep checking beyond that. I utilized this to report the time difference in form submissions down to the seconds. Just shar…Formulas and Functions196[email protected]

SUMIFS with Multiple CriteriaQuestionAnswered  Pending Review Fiona Lee 13 views 1 comment 0 points Most recent by Mike Wilday Formulas and FunctionsFirst post 05/28/20,Last post 05/29/20Hello! My ultimate goal is to be able to get a sum of the inventory per item, by month. For example, I would like a formula to return the amount of Control Panels there are in June 2020. Based on the screenshot below, the result I should r…Formulas and Functions131Fiona Lee

Accepted Answer Jeana 18 views 5 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 05/28/20,Last post 05/29/20I want to populate a name(s) in a Contact list based on the value of another field. For example: If a cell contains "Copyediting" I want Holly's name to be added to Additional Assets (a contact list). Thanks, JeanaFormulas and Functions185Jeana

how to get ° degrees circle symbol in a cellAccepted AnswerAccepted Answer Lisa_Doris 13 views 3 comments 0 points Most recent by Lisa_Doris Formulas and FunctionsFirst post 05/28/20,Last post 05/30/20How do I write the degree symbol and is there a sheet with all symbol shortcuts available somewhere, not just the most popular?Formulas and Functions133Lisa_Doris

Answered  Pending Review Fernando Flores 13 views 4 comments 0 points Most recent by Fernando Flores Formulas and FunctionsFirst post 05/28/20,Last post 05/28/20I'm working on developing a template workspace for our PM to use, that will eventually roll up to a PMO workspace. We plan on getting licenses for the PM if we go to production after the pilot. Each PM has multiple clients so they would …Formulas and Functions134Fernando Flores

Answered  Pending Review ICue 11 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 05/28/20,Last post 05/29/20I'm trying to figure out a formula to return the value in a cell over multiple columns. I have 10 locations consisting of various buildings for each location. Only one column will have text in it. The formula I have will obviously only re…Formulas and Functions113ICue

First post 05/28/20,Last post 05/29/20I would like to have the parent row of my health column automatically be red, green or yellow based on the average health of it's child rows. Does anyone have an idea of how to accomplish this?Formulas and Functions102Cara Sikes

First post 05/28/20,Last post 05/29/20I would like to have the parent row of my health column automatically be red, green or yellow based on the average health of it's child rows. Does anyone have an idea of how to accomplish this?Formulas and Functions101Cara Sikes

Predecessor Plus One YearQuestionAnswered  Pending Review Ezra 14 views 1 comment 0 points Most recent by Sean Morgan Formulas and FunctionsFirst post 05/28/20,Last post 05/30/20I've been using 52 weeks (for example: 17FF + 52w) to give me a 1 year warranty expiration date from the end of the project gantt. It would be nice to have a full year option, like this: 17FF + 1yFormulas and Functions141Ezra