Formulas and Functions
Archives for February 2020
Discussion List

Is it possible to add dynamicaly a line ina summary sheet if a new sheet is created in a folder?Answered QuestionAnswered emmanuel edon 21 views 8 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/29/20,Last post 03/02/20Hi there, i manage several projects. Each project is in a individual sheet. I created a summary sheet to gather information from projects. When i create a new project in a folder, i wonder if it is possible to add this new project inside t…Formulas and Functions218emmanuel edon

Excluding Multiple OptionsAnswered QuestionFirst post 02/29/20,Last post 03/02/20I am having a real challenge with a particular function. I'm thinking someone will know this right off the top of your head. =SUMIFS({Digital Marketing Workflow Total Hours}, {Digital Marketing Workflow Priority}, <>"SubCate…Formulas and Functions181CTSr

IF Statement helpAnswered QuestionFirst post 02/29/20,Last post 02/29/20So, I am trying to create a column that adds the Student ID, when the Student is Named. This way I can make my reports deidentified. From a workflow standpoint, Teachers use a drop down for the Names on a Form and then enter Student Progr…Formulas and Functions101DMS_2020

Accepted Answer emmanuel edon 32 views 6 comments 0 points Most recent by emmanuel edon Formulas and FunctionsFirst post 02/29/20,Last post 03/09/20Hi there, I need to know if it is feasible to obtain this formula : In a sheet, i can choose several option from a list of value ( checkboxes). If option A is checked, i add 1.5 days for ex. If i choose B, i add 1 day. If i choose A & …Formulas and Functions326emmanuel edon

Using VLOOKUP for Approval Requests Across SheetsAccepted AnswerAccepted Answer Darin Smith 30 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 02/28/20,Last post 03/09/20GOAL: 1) Have a secure and errorfree VLOOKUP on Sheet B and 2) Run a multilevel approval request automation on Sheet B. DETAILS: I have 2 sheets. Sheet A tracks contracts. It has columns for individuals (contacts) who are designated appr…Formulas and Functions303Darin Smith

Changing % complete using a formula in another cellAnswered QuestionFirst post 02/28/20,Last post 03/02/20I know you cannot use a formula in a "% Complete" field. Is there a way to update "% Complete" from a formula in another column (something like a "helper" column)? I have a "Task Status" column wit…Formulas and Functions192BJ Patnode

Accepted Answer Ann Foley 19 views 3 comments 0 points Most recent by Genevieve P Formulas and FunctionsFirst post 02/28/20,Last post 03/08/20Hi, I'm trying to make a formula that says 2020 Experiential Audit minus 2019 Experiential Audit = value in Experiential Audit YOY change...but only if there is data in both 2019 and 2020. See my sheet below. I think this is an IF formula…Formulas and Functions193Ann Foley

Can I lookup a value based on two criteria?Answered QuestionFirst post 02/28/20,Last post 03/02/20Hi , I have two sheets, sheet 1 holds fixed data and sheet 2 is populated via a form. I want a cell in sheet 2 to return data from a cell in sheet one when two column values in sheet two are matched to two column values in sheet 1. Is this…Formulas and Functions281Richard King

Duplicate value formulaAnswered QuestionAnswered Heather Druckenmiller 28 views 1 comment 0 points Most recent by JReisman27 Formulas and FunctionsFirst post 02/28/20,Last post 02/28/20I am trying to use a formula that will place a check mark in my duplicate check column if an invoice# in column 8 is a duplicate. I was using the =IF(COUNTIF format but it doesn't seem to be working for me Any recommendations This is th…Formulas and Functions281Heather Druckenmiller

Formula for % of checked boxesAnswered QuestionFirst post 02/28/20,Last post 03/02/20I'm requesting help to create a formula that will calculate the % of the checked boxes that are checked vs unchecked for calendar year with a project status of complete?Formulas and Functions263Alisia Gill

sequential lettersAnswered QuestionFirst post 02/28/20,Last post 03/10/20I have a sheet where I have parent and child rows, and I would like one column in the child rows to have sequential letters. I have a picture here. I have a parent/child column that would normally be hidden, but I count the number of chi…Formulas and Functions225Eric Figge

Accepted Answer Katie White 23 views 4 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/28/20,Last post 03/03/20Can someone help point me in the right direction of where to go to submit a ticket for this? For four rows on my sheet (Row 1, 3, 4, 5) every time I save my sheet those rows update the modified date, even though I'm not touching those rows…Formulas and Functions234Katie White

Accepted Answer Tyler12345 39 views 10 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 02/28/20,Last post 03/03/20Hello, Apologies for yet another basic question, but I'm having troubles wrapping my head around how to get a formula to behave a certain way. I'm still working on a PTO manager tool (requests PTO, automated approvals, and auto calculation…Formulas and Functions3910Tyler12345

MULTIPLE AND ON MULTIPLE COLUMNS wANCESTORSAccepted AnswerAccepted Answer ncanales 18 views 2 comments 0 points Most recent by ncanales Formulas and FunctionsFirst post 02/28/20,Last post 03/02/20Hi there... really need help 😯 I have a list of actions, all actions are either a PARENT action (Outdented) or child actions (Indented). I added a column with =COUNT(ANCESTORS()) to have 1,0 depending on the case. I have another column wi…Formulas and Functions182ncanales

Circular Reference Warning When LinkingAnswered QuestionAnswered Adam Fleming 14 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/28/20,Last post 02/28/20I'm receiving a circular reference warning when all I'm doing is pasting as links to copied cells. Any ideas for why this is occurring when many other places in the sheets are able to be copied in this same manner?Formulas and Functions141Adam Fleming

COUNTIFS with other IF criteria and referencing other sheetsAccepted AnswerFirst post 02/28/20,Last post 02/28/20Hello! Context: I'm leading the charge on tenant migration for a datacenter over 7 weeks. There are 10 teams (each with their own inventory sheet) and hundreds of services among all the sheets. Goal: track completion metrics across all te…Formulas and Functions351kolfinna

Force upper case on a columnAnswered QuestionFirst post 02/28/20,Last post 03/15/20Hello Everyone, I got a question. Is there any way to force upper case on a text column? I have a column where users enter information using iPads and it will be very helpful if the cells will automatically force to have upper case font i…Formulas and Functions305JuanJLC

Limit number of rows in Smartsheet?Accepted AnswerAccepted Answer Michael Tuccillo 24 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/28/20,Last post 02/28/20Hi there, Is there a limited number of rows in one smartsheet? If so, what is the number? Thanks! MikeFormulas and Functions243Michael Tuccillo

IF Multiple values selected THEN VLOOKUP and return valuesAnswered QuestionAnswered Stephanie Bauder 19 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 02/28/20,Last post 02/28/20Is there a way to return values in a cell if multiple criteria is met? I would like to track the amount of time our Onboarding Coordinators spend on tasks for each Country. So when they create a contract in Austria it takes 60 minutes but …Formulas and Functions193Stephanie Bauder

AutoFill Rows of Data Based on CriteriaAnswered QuestionAnswered Stephanie Bauder 30 views 3 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/28/20,Last post 03/01/20Is there a way to have the Sheet Summary autofill multiple values based on a selection from a dropdown field? I would like to have "country" be the dropdown select field and based on the country selected, the fields below woul…Formulas and Functions303Stephanie Bauder

Why is the same exact function behaving differently for 20 rows?Answered QuestionAnswered Kimberly Jones 22 views 6 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/27/20,Last post 03/01/20I have the following function on over 140 rows in a project plan: =IF([email protected] = 1, "Task Done", IF(ISBLANK([email protected]), "Ready to Begin", IF(INDEX(TaskCheck:TaskCheck, VALUE(LEFT(([email protected]), 2))) = …Formulas and Functions226Kimberly Jones

Formula to Trip a FlagAccepted AnswerAccepted Answer Michele Phillips 32 views 4 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 02/27/20,Last post 02/28/20Hello, I currently have a formula in my sheet that trips the Flag function in my "At Risk" Column if the due date is less than today, and the status is NOT waived. The formula below works great. However, I would like to includ…Formulas and Functions324Michele Phillips

SUMIFS with sheet references.Accepted AnswerAccepted Answer Richard Lucas 26 views 4 comments 0 points Most recent by JReisman27 Formulas and FunctionsFirst post 02/27/20,Last post 02/28/20Hello, I've got this formula working like this. =SUMIFS({DayCount}, {EmployeeName}, [Employee Name]@row, {ReasonforAbs}, "Vacation") However when I change the criteria to "PPL / Sick" it's returning 0 when there are cl…Formulas and Functions264Richard Lucas

Contact List appearing as email address and not by nameAccepted AnswerAccepted Answer Silvia Rangel 32 views 3 comments 0 points Most recent by JReisman27 Formulas and FunctionsFirst post 02/27/20,Last post 02/28/20Hello, Has anyone encountered this issue? I have a contact list column and the values were entered with a name and an email, when pulling a value into a column sometimes it shows as a name (which is how we wanted to show) and other times …Formulas and Functions323Silvia Rangel

Accepted Answer Mary Hum 22 views 3 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 02/27/20,Last post 03/03/20I want to use add up costs in an AMOUNT column when the value in the CATEGORY column is SHOES and this display this in a Summary Order sheet. Can someone tell me what is wrong with the syntax of this formula: =SUMIFS({REFERENCE SHEET}, A…Formulas and Functions223Mary Hum

How to count DISTINCT countries within a date range?Answered QuestionFirst post 02/27/20,Last post 02/28/20Quarterly, I am required to show how many DISTINCT countries were reached by our instructors. I need a formula that would count the DISTINCT countries reached between (for example) 1/1/2020 thru 3/31/2020 (for first Quarter reporting). I'v…Formulas and Functions201Kitty

Friendly RemindersAnswered QuestionAnswered David Gómez Roy 22 views 1 comment 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/27/20,Last post 02/28/20Hi there, I have two troubles with the tool, I hope you can help me and I'll grateful for that First: I'm automatizating my process with friendly reminders, but these one emails their looks like a spam emails, I mean, the vowels with t…Formulas and Functions221David Gómez Roy

IF THEN using multiple sheets and multiple columnsAnswered QuestionFirst post 02/27/20,Last post 02/28/20I am looking for a formula that brings over data from a column only if there is a date in another column. =IFERROR({Dallas Tracker NEW Start Date} > 1, (IFERROR(INDEX({Dallas Tracker NEW Mileage}, MATCH([SEGMENT ID]525, {Dallas Tracker …Formulas and Functions231noclue

Use Conditional formatting to mark cells that contain formulasAnswered QuestionAnswered RPIArchShop 39 views 6 comments 0 points Most recent by Andrée Starå Formulas and FunctionsFirst post 02/27/20,Last post 03/15/20Good Afternoon Smartsheet wizards, While I am relatively new to Smartsheet and have purchased some of the learning, the Conditional Formatting is entertaining. I'd like to prove that certain cells have formulas, or not. Thank you in advan…Formulas and Functions396RPIArchShop

Combining cells from one sheet into a single cell on another sheetAnswered QuestionAnswered John Kennedy 27 views 9 comments 0 points Most recent by Paul Newcome Formulas and FunctionsFirst post 02/27/20,Last post 03/02/20I have a master spreadsheet with many columns (sheet1). From that, I want to create a second sheet with fewer columns (sheet2). The first column in sheet1 is 3 cells combined (=[Column1]1 + "" + [Column2]1 + "" + [Col…Formulas and Functions279John Kennedy