-
Multiple nested IF(AND) paired with INDEX/MATCH ? or other way ?
Hello to everyone. Im starting to go deeper into advanced formulas and sheet reference, but have some issue... I have a "customer activity" sheet, where for each line, there is 2 sets of variables (5 in "activity", 3 in "types"...) and a "Duration" column. I would like to compute the "Final Price", by referencing a "price…
-
Formulas not working after adding year?
I'm getting one of our dashboards updated for the new year but the previous year didn't have the year part of the formula. I've gotten it added for most but I've run into a couple where when I add the year piece I can't seem to get it to work: =COUNTIFS({Relevant #}, 1, {Submission Date}, IFERROR(YEAR(@cell ) = 2025))…
-
Inventory check
Help please! I have an inventory issue I need to work on. I have a production schedule where my production team adds line items for the PO they are supposed to work on. Each Line item may have the same item number, and could have the same quantity. I am using data shuttle to pull in my available inventory. From there I am…
-
Return multiple contacts based on department dropdown list.
Hello, I would like to use a master list with a list of people attached to the department that populates multiple contacts in the same cell depending on what dropdown departments are selected. So Sheet 1: Department Email Marketing Billy@help.com Operations Bob@help.com Sheet 2: Departments (dropdown multi select)…
-
Invalid Reference
I am trying to pull data from two sources to populate a new sheet. The source sheet is broken into two sheets (A-G and H-Z). The rows that need to reference the A-G sheet are returning fine, but the rows that need to reference the H-Z sheet are coming back with an Invalid Reference. This is the formula: =IF(AND(ISTEXT([FI…
-
Calculating Critical Open Items per Business Analyst
I am trying to calculate how many Open items a business analyst has that are in the critical category, this is the formula i am trying to use but i must be doing something wrong Open equals = To Do, In Progress, Daily Task =COUNTIFS({2025 Team Activity Tracker Range 3}, HAS(@cell, "BA NAME"), {2025 Team Activity Tracker…
-
How Can I Automatically Remove Outdated Rows for Updated Project Data?
How can I automatically remove outdated data from Sheet A in my Smartsheet, which pulls project data (weeks remaining and timeline variance) from multiple project summary sheets? Whenever a project's data changes, Sheet A adds a new row with the updated information, but I want to ensure only the most recent data is shown…
-
PMO Template
I am struggling with the PMO template and the Schedule Health column in the Project Plan, which is indicating 'Red' even after a Task is complete, with this formula. =IF([Schedule Delta (%)]@row > 0.1, "Red", IF([Schedule Delta (%)]@row > 0, "Yellow", "Green")) Could I use a formula to ask it only to count if "Status" is…
-
Office Clinic Patient Registration Booking
I am looking to set up a booking system for a downtime project to be able to use in our offices (clinics). I would like to be able to see the rooms for that location and the availability for each time slot. Ultimately if a patient checks in they would then be assigned to a room during registration. 2. Is there any…
-
Formula result can't be copied and pasted correctly
I am using a formula that takes the value from several columns to generate a job name and number. It works beautifully but for some reason, it's putting a space after each "_" and so I have to use "wrap text" to display appropriately. However, we used to be able to copy the job name generated and paste into another…