-
Displaying all unique values on a new sheet from columns on a reference sheet - Help
We have a form set up for collecting information from scientists performing runs by different projects. We need to be able to supply material lot information to clients separately by project numbers. I tried to use the following forums to perform the index/collect/distinct formulas which didn't work for me when referencing…
-
Index Match formula = unparseable
Very new user to Smartsheet. Two questions: What formula change needs made to not get error? Would it be better to do index and collect? Sheet: Fields Appointments: Appt #, Date, Time, MRN, LLL Language, MRN Language lookup (field populating) MRN Additional Information: MRN Master, MRN Language, Eff date, Last updated…
-
Nested ranges in INDEX/COLLECT??
I need to pull in all of the email addresses from a sheet into another sheet so that my workflow can alert the people listed in that cell. I have the cell set up as a contact list allowing multiple contacts. I need to pull all the emails for "Anywhere US" where the level is "Major" into the sheet that has my workflow when…
-
Sum a column associate with children
I have the following layout, and I am trying to sum the "Ordered QTY" column for all children within each parent in a helper column so I can verify that amount with the parent "Ordered QTY". Any help would be appreciated and thank you very much…
-
Adding Multiple Criteria to a COLLECT Function
I have a working formula gathering the MEDIAN of a group of numbers that fall within a date range used in a trend chart in a dashboard. I now want to add two additional criteria for cases when the dashboard user would like to calculate the MEDIAN but exclude certain values (referred to as 'HOLD' and 'Strategic' data) from…
-
What is the function of number one at the end of this formula?
=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1) Thank you!
-
Formula for "Due date = 3 business days before Deploy date?"
Hi, I would like to build a formula with the following logic: "Due Date = 3 BUSINESS DAYS prior to Deploy Date." Building a "3 DAYS prior formula is simple enough, but I'm wondering if anyone has solved for logic that can only offer a day that's Monday thru Friday. I appreciate any advice! -Adam
-
COUNTIF in an IF formula
I am trying to see when a segment is fully completed. Our segments can be broken out into subsegments, and they can have up to 5 subsegments, indicated by the segment number followed by a "—1", "—2", and so on. So far for this formula I've gotten it to count how many subsegments there are, but can't get the final push of…
-
Index Match with multiple Parent/Child rows in a sheet.
Hello all, I'm looking for some advice on a tiered IF statement using index/match and CHILDREN formulas. My case: I receive numerical data from another metric sheet that copies it snapshots into this archive once a month. There may be times where we need to edit the historical data. To ensure the % columns are updated I…
-
Formula to Count Ages of Orders
I'm trying to create a formula that counts the open orders based on the date the order was created. The thing complicating the formula is that it's for a metrics dashboard, so I need it to count the orders that are between 1-3 months old, 3-6 months old, 6-9 months old, 9-12 months old and over a year. I'm at a complete…
