-
One of my criteria is one of many listed in a dropdown column and CONTAINS/HAS are eluding me.
Hello! I am working from a single "Master Sheet" and utilizing INDEX/COLLECT to pull the desired info onto separate sheets. I am utilizing "Feature", "Level" and "Product Line" as my criteria, however the "Product Line" is a multi-select dropdown column with anywhere from 1-10 values in it depending on the products usage.…
-
[Need Help] Lookup/Index matching for Horizontal Data
I’m trying to automatically assign a reviewer type (like “Peer” or “Leader”) based on a reviewer’s phone number. The problem is that in my reference sheet, the 20 possible reviewers are listed across columns — each one has a phone number and a reviewer type in a separate column. In my submission sheet, I just have one…
-
Pulling data from another sheet based on two criteria
Hello! I am having trouble getting this formula to work.. The goal is to pull the quantity of an item from a reference sheet to this sheet if the Spec #@row on this sheet matches an EAN on the reference sheet, and only if the Disposition value reads "Released". This is the formula I have cooked up, but get an…
-
Summary Sheet & Filter based on Form Responses with Multi-Select Dropdown
Hi all! Hoping for some assistance with a summary formula pulling in criteria from a multi-select dropdown response form... I have a dashboard that has a form embedded in it to allow the user to enter their desired criteria (see below screenshot) and then the bottom of the dashboard pulls in the metrics using the form…
-
Using Collect to pull data from the same sheet
I usually use Index(collect to reference separate sheets to pull data, but I'm trying to apply the same logic for pulling column values from the same sheet. I tried this as an Index(collect formula =INDEX(COLLECT([QTY on Order]:[QTY on Order], [Ref PN 1]:[Ref PN 1], [Part Number]@row),1 & a collect formula on its own,…
-
Strange INVALID VALUE error
Hi everyone, I've encountered a problem in Smartsheet that I tried solving for a long time without success. I was creating metrics for a sheet, and I made a bunch of summary fields that contained my formulas. They all worked as intended when I created them. About a month passed without anyone touching them, I went back to…
-
INDEX COLLECT function returning #INCORRECT COLUMN VALUE
I have a sheet that contains the info of all of my employees which includes a column for their initials. The training department of my company has a sheet that I have view access to but not editor access. They track the qualification due date of all of my employees and I want to collect the due date on my employee info…
-
How to add another calculation to an If statement and Index/Match
Hi, Hopefully this will be an easy one. My brain is going completely blank on this. I have a formula that is basically calculating/selecting one of three values based on which is not empty. It is based on the user checking the "Included" box to trigger an index/match formula to show the cost (Hourly, ,Unit or Fixed), and…
-
Cross sheet functioning column formulas working perfectly...except for two rows?
Problem Summary: I have a series of formulas and cross sheet references which grab display data that meet certain conditions. They are working consistently across usage…except for two rows? For data sensitivity, I'll only be including the date fields out of the display data, but the same error applies to all final display…
-
Indexing & Sheet Updates
Anyone that uses sheet to sheet indexing noticing that sheets are not updating unless you open, save and refresh the data sheet that is the source sheet? We have had several team members reach out to let us know that their dashboards and reports are not updating due to this situation.