-
Get a distinct set of values from an existing column without changing order.
Hello, I am trying to get list of values that has each value only once. Below I have my JOB NUMBER column and I am trying to create an automatically updating column that we will call DISTINCT NUMBER that lists each number once. The catch is that the JOB NUMBER column is always changing and I want the DISTINCT NUMBER column…
-
VLOOKUP Based on Name and Date
Hello, I am having some trouble with a query allowing me to look up values based on the name of an individual AND within a certain date range. The data table is weekly updated with new values, and has multiple fields, including name, date and comments. I need to pull a comments field, based on name and date. Thank you in…
-
Add
I have 3 Sheets: #1 = Closeouts, #2 = Annual Incentives; #3 - Incentive Distribution When a closeout is completed, data is entered on Sheet#3 - Inc. Dist. I have a Data Mesh to pull the data from the # 3 to populate Info on #2 Ann Inc. I am trying to get a formula or function that will do the following: When an entry is…
-
I was wondering what I would need to do to make this formula return the accurate number?
At the moment, I have a formula that is not actually doing what I need it to do. Basically what I have is an inventory sheet and when folks remove from the inventory I need it to look at the cost of that item from the cost column in that specific row and return a total $ Amt Used. However, occasionally, the item is…
-
Avgerages, assigning a value to text, and parent/child formula
I'm attempting to insert a formula into parent rows that assign a % complete based on entries on child rows. The child rows responses are locked based on task completion. The options are: "Yes, In-Process, and Not Started." I've made these two stand-alone formulas that I "think" will work, but I can't get them to play…
-
#INVALID DATA TYPE for some functions using DATE
Hi: I am trying to generate a sum (cumulative) of all effort "points" for projects started in the current year. CASE 1: My reference columns are: "Project Start" (date) and "Effort Count (Est. Total)" (text/number). I used the formula: =SUMIF([Project Start]:[Project Start], YEAR(@cell) = 2020, [Effort Count (Est.…
-
Formula: Turn Sprint # into Date or vice versa
I'm attempting to create a formula that will translate Start Date into sprint number. In my spreadsheet there is a column for Sprint that I'd like to populate it with Sprint 1, Sprint 2, Sprint 3, etc based on date. For example Sprint 1's start date is 12/11/19, Sprint 2's start date is 12/25/19, etc, increasing in two…
-
Adding formula to existing formula for status change
Hi everyone, I currently have a formula in my status column that auto-update depends on the percentage completion. Anything that is 0 is Not Started, and greater than 0% and less than 100% is Green (G), and anything that is 100% is Complete (blue). I want to add 2 more actions in the formula. If Finish Date is in the past,…
-
Insert Formula within Workday "num_days" string
Hey, This may be super simple & this feature is available in excel, so I think i am just missing something in front of the "-1" for Smartsheet to pick up a formula. I am trying to calculate the day for a "test" shipment to arrive, based on 3 factors - 1. workday (with holidays), 2. Scheduled delivery date of devices, and #…
-
Need Workaround Ideas for Automation Condition
Client submits file through form. I have an automation set up to respond to client when new file w/response is attached to row and "Status" and "Date" cells @row are populated. It works as intended but relies on people to always set those 2 fields and save before attaching the file to the row. Without the conditions set,…