-
My first VLookup
Hello, and thank you in advance for your help. I am having trouble displaying data using a VLOOKUP function (I have never used one). My formula reads as follows: =VLOOKUP(Predecessor@row, {PPPredTask}, {%Complete}, true) Its goal is the following: Based on the value in the Predecessor Column in my sheet, I would like to…
-
Date range calculation, count month numbers
Hi All Could I please ask for some help with a date formula? I'm working on an online education project. Each module takes around 6+ months to complete, see below the date range that starts on 14 July 2020 and ends on 25 Jan 2021. I need to count how many modules occur in January, how many in February, how many in March,…
-
Multiple Select Contact Column/Sheet Summary
I'm looking for a formula that I can use in Sheet Summary to identify individuals assigned to the same task via a multiple select contact column. The below is what I currently use to track daily tasks for individuals. Target Tasks =COUNTIFS([Due Date]:[Due Date], =TODAY(), [Critical Ops.]:[Critical Ops.], ="Yes",…
-
Lookup contact name from email address
Hi, Users enter data into a sheet via a Form. One of the columns is a "Created By" system column. This provides me with the email address. Is there a function to lookup the name from the contact list based on the email address. I'm aware that I could achieve something similar by have a second sheet with Names and email…
-
counting multiple select column
Hello, I have a project plan that I'm trying to report on. I have a column for health check and a column for assigned team. I want to see a total count of each status per team. That is the easy part and I figured that out by doing a =COUNTIFS({HealthCheck}, "Red", {Team}, OR(@cell = "FI", @cell = "FIT")) My issue is that…
-
IF formula to Assigned To
I am having trouble trying to figure out the formula based on the example: If the ISSUE TYPE is PTO, then assign it to XXXX. I have one column labeled "Issue Type" with a dropdown selection, and the other column labeled "Assigned To". Please help!
-
Red / Yellow / Amber formula
Hi, Can anone please suggest for my sheet that is formatted as shown in the attached image and for the following conditions if risk/issues cell is not blank and due date is in the future then yellow if past due date then red if past due date and with risk/issue cell not blank still red if not past due date and no…
-
How do I count occurrences of a word in a row?
I'd love to count the number of times the word "below" occurs in a row. Apologies for the simplicity of this question, I'm very new to Smart Sheets! This word could occur as either "below target" or "significantly below target", but I'm hoping just to count ALL instances of "below" once if that's possible. I've tried…
-
Partial Text Search in Range - Index/Match
I have searched for solutions and getting weary. Challenge find PO# (a 6 digit alpha-numeric) in a string of text (GL_Remark on another sheet to index the actual value cleared general ledger. I had this formula working in Excel: =INDEX(General Ledger_AmtPaid,MATCH("*"&[@[PO/INV]]&"*",GL_Remark,0)) In SmartSheet Only finds…
-
Help pulling data from certain date range
I am currently pulling data from a mainsheet into a dashboard via a metrics sheet. Its working fine but I have been asked to start only showing data from the last 30 days on the dashboard. I am having problems figuring out how to add a date formula to my current formulas. An example of my current formula is…