-
Average that meets Criteria 1 or Criteria 2
I have a smartsheet with many events that often span several months. I want to calculate the average age of relevant events for each month. For example, for the month of March, I want it to calculate the average age of events that are still open in March in addition to the age of all events that were closed within the…
-
How to Identify Duplicate Job Numbers on Multiple Sheets?
I manage 30 production projects across three Smartsheet sheets: one main sheet (Project Management) and two spin-off sheets (for Ordering and Install Certification tracking after a project ships). All sheets use a unique 4-digit job number for each project. What’s a formula or function I can use to quickly identify if any…
-
Formula to Identify Number of Forms Submitted by Individual Offices
Hello, I am working on building a dashboard to display global and individual data regarding different requests (out-of-state travel requests, official functions, etc.) across seven offices. I would like to eventually calculate the monetary value of each of these requests, labeled as 'Amount' on the reference sheet. I am…
-
Updating Previous and Latest checkboxes based on a value in another column.
Hi & Happy Friday, I'm putting together a status reporting sheet and I need to be able to update the previous and latest rows, based on a separate column, in this case the Submission Status column. Formula in the Previous column is: =IF(([HC-Row ID]@row + 1) = MAX([HC-Row ID]:[HC-Row ID]), 1) Formula in the Latest column…
-
Index Match Returning Unexpected Value
I feel like I've posted 1000 Index Match questions this year! I have a formula (below) that is returning and individual who has the same Date Assigned field but not that same MLL ID. The MLL ID is supposed to be the thing that stops the date range from looking at all the cells. I'm trying to get Ciara as a returned value…
-
Creating A Report of Missing Submissions
Hi Community! I have a form deployed to community partners. These partners need to submit a report 1x per month. Of course, some do not submit the monthly report. Is there a way for me to create a report of missing submissions? I have a reference of all partners who should be submitting a report and the sheet from the form…
-
Using IF/THEN for my formula
Hello, I recently received advice on here to develop a formula that lists out all projects that individuals are billing to via cross sheet formulas. The below formula would go into the Projects column in the Enterprise Rollup. =JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", ") This formula works, however, it is…
-
Return a value based on the date and section
Hi, I'm trying to create a sheet for a department production report, This department has 10 sections, each section do different type job but the project is same, i'm keeping all section work in a mastersheet so that i can filter however i want to generate a report. the Problem i'm facing is the closing stock should be…
-
Sum data and not change data after certain date.
I am trying to sum data as of XX/XX/XXXX of each week. I want this data then to go into a cell and not be changed the next week. For example, on 1/15/24, a sum formula was used to calculate the number hours remaining was 3891. On 1/22/24, the sum formula was used to calculate the number of hours remaining as 3201. Is there…
-
IF with INDEX / MATCH
Hi. I use INDEX/MATCH regularly, but want to add an "IF" statement and am getting #UNPARSEABLE, and would like your help. I'd like to pull the "F Name" from Sheet 2 into Sheet 1, when the "Info Level" in Sheet 2 = "Primary". This formula returns #UNPARSEABLE: =IF({Sheet 2 Info Level Range} = "Primary"), INDEX({Sheet 2 F…
