-
Count Distinct is Clearly Incorrect.
I have a sheet with approximately 18,000 rows. One column is called "ID". I have a support column where I am trying to understand how many unique entries are in "ID" and I am using this formula: =COUNT(DISTINCT(ID:ID)) It's returning a value of 1 (which is clearly not correct). The column is a text column. There are no…
-
Need a space saving IF OR formula
I have a working function to assign a school county to a new entry when a certain institution ID is put into the column. My issue, however, is the complete equation to accomplish this is too long and doesn't fit the last few school districts. I'm checking to see if there is a more elegant way to accomplish this so I can…
-
Formatting Column to Display Certain Number of Digits
Hi all! Happy New Year :) Hoping for some help with formatting numbers… For context: Employee ID #s at my company are all 8 digits, so if someone's ID # is 123456, we add leading 0s to the front so it would be "00123456" making all Employee ID #s 8-digits. QUESTION: Is there a format-feature or formula I can use to adjust…
-
Duration and Date closed columns auto populate when Date initiated column is filled in
I have "Date Initiated", "Date Closed" and "Turnover Time" columns. The initiated and closed columns will (should) be populated when they happen, but the closed column auto populates to the same date as the date initiated column when it gets filled in. I also had to create a "Predecessor" column which I hid because I don't…
-
How can I update data in one sheet from another sheet?
I have a New Hire Master sheet to keep track of our New Hire's onboarding tasks. The first eight columns contain data that needs to be pulled from the New Hire Master Sheet over to the Membership Tracker sheet. I'd like to use a formula to update the the 8 columns in the Membership Tracker with the latest data that is…
-
Formula to calculate an average using 12 most recent form submissions that meet certain criteria
Hello Community! I have a database where 11 different units submit data on a monthly basis related to filled FTEs vs. open FTEs so we can determine the monthly vacancy data. I have built out a form that the managers from each unit use each month to submit their filled FTEs for the previous month. Smartsheet then uses…
-
In need of a formula that takes the amount in the Children rows and divides by a Parent row total
I am in need of a formula that takes the amount in the children rows and divides by a Parent row (ideally would make a column formula). My current formula for the attached is the below (one of many formulas tried). =IF(CHILDREN([Merchant Revised Casepack Quantity]@row > 0, [Merchant Revised Casepack Quantity]@row /…
-
Pull value from cell to a new cell, and it won't change even if source value changes
I will use the following as an example for my problem: I have a pre-existing sheet ("Team Employees") where different teams record their # of employees once a month. This was used to track live staffing. The request is now to track overtime. My question is this: Is there a way to pull the # OF EMPLOYEES for LOGISTICS from…
-
Need a better Sheet Summary Formula - (Collect/Max & Index/Match)
In my example, I am able to return the top Sales for an item based on certain criteria for a Sheet Summary formula (Shirt Top Sales) with =MAX(COLLECT(Sales:Sales, Product:Product, "Shirt")). I am also able to return the Color for the Top selling Shirt (Blue) with a formula using the previous summary value in the formula:…
-
Need another set of eyes on SUMIFS formula in Sheet Summary
Hi and Happy New Year, Hope all is going well for you. In the Project Intake Sheet, I'm using the Sheet Summary to calculate metrics for reporting. On this particular formula, I keep getting #UNPARSABLE. I haven't been able to figure it out all day. Appreciate any input you may have! Goal: To sum the values in the ARR…