-
COUNTIFS and SUMIFS formulas with multiple criteria AND a dynamic date range in a sheet summary
Hi everyone, I've had a look in the forum threads to try and find an answer already, but not sure what I'm trying to do is like-for-like with some of the suggestions. BACKGROUND: I'm helping colleagues with aggregating some of the data in their deployments sheet, which they use for managing and tracking their events, the…
-
Index/Match in different scenarios on same sheet
So far I've been successfully working with index/match to pull certain names (PM/Supervisor, Additional CC) when a certain requester is used on a sheet. Now I have a requester that is assigned to two (2) different clients, but the PM/Supervisor and Additional CC are different people per client. How can I incorporate that…
-
Search and Replace in formulas
When importing from Excel I need to fix some of the formulas. This would be easy if I could use a search and replace within all the formulas on the sheet in one go as you can in Excel. Does anyone know if there is a way to do this in Smartsheets?
-
Identify Most Recent Record
Hello, I am using SmartSheet to log Training Records. Every time a record is submitted, I am recording three fields: Date, Name, Topic. Based on the date, I am projecting forward the next time training needs to occur. (ie: 3 years after most recent training record). This occurs in a "Renewal Date" Column as a column…
-
Make Single Line Form Entry into 18 Separate Rows
I have created a process using Helper Columns, Formulas and Automation that takes a 1 row form entry copies it into 18 separate rows based on the Sport Column and then copies those 18 rows into a 2nd sheet data sheet and then moves the entered row (Row 1) to an archive sheet. The theory was that when someone submits a form…
-
Parent row status formula based on child rows
Roll up formula for status I have this formula (shown below) where I am trying to get the status of the parent row automatically entered based on the child row. I'm not sure if I've stared at this too long and my eyes are crossing, but it's not doing what I want it to do. I think I've listed all the permutations below. 1.…
-
Getting data calculations from another sheet
I am trying to create a metrics sheet for my dashboard of how many issues in these categories below, are High Medium Low Open Currently in Pilot Pending Development Pending Production Release Pending Dev Acceptance Review with Third Party Under Investigation Closed I am trying to gather the data from another sheet, but i…
-
Calculate average of mid-range
I am looking to find the average of the middle 50% of a range - excluding the lower 25% and the upper 25%. I am trying to add the formula into a metric sheet, so the formula would reference another sheet. Here is what I thought would work but does not: =AVERAGEIF({Permit issued to constr start}, ">= " & PERCENTILE({Permit…
-
Challenges with combining SUMIFS and COUNTIFS
I am using the Sheet Summary to input formulas helping me to track the COUNT of installs that took place in a given month/year. But now I want to SUM how many assets were installed at that location using the same date criteria. My formulas below are allowing me to COUNT how many rows (AKA customer locations) fit my…
-
Allow Images, Field Formula Calculations, and Layout Customization in Forms
Allow Images: It would be so great to be able to add images to the form layout that can be viewed within the form. This could be used for multiple purposes (ex. Order or Item selection based on image. Ex. Provide visual instruction or assistance to help user complete the form.) Field Formula Calculations: If a form's…