-
Collect cross sheet formula help
I want to pull through the task name in one sheet where the check box at row is checked into another target sheet. This is what I though the formula would be but I get #UNPARSEABLE as the error. =INDEX(COLLECT({Task name}, {Stage check box}@row , 1)) Um, help please?
-
INDEX COLLECT over multiple documents
I am looking for your help once again :) So the below formula works fine for me however I have other sheets that I also need to reference. I'm assuming I would need to add an AND or OR formula to the below however I'm not sure where or what structure the formula should be. And its to reference another 4 sheets, including…
-
Multiple criteria for Index/Match (or collect?)
Hello, I am trying to return data from a cell in a reference sheet based on (2) criteria - one at the row, and one in the 1st row of that column. I have metrics sheets that accomplish a similar function but those are using sumif for calculations, whereas here I just need to pull in text. How do I create an index/match or…
-
Help for INDEX/COLLECT with multiple criteria
Hi everyone! I usually use INDEX/MATCH, but I am trying to "match" multiple criteria and found that it is better to use INDEX/COLLECT when dealing with multiple criteria. I keep returning "#INVALID VALUE" and am confused what I am missing… I have an "Audience" sheet my team is using with employees' respective name, ID…
-
INDEX COLLECT MIN (again)
Had a follow up question to my previous question located here. — I was using the original sheets, which I fixed with the solution from that thread, as a template. Today, I copied them over to essentially create the same system for a different LOB. I checked the box that updated all formulas to the new references. Now, in…
-
Lookup Formula Not working
I am attempting to do a lookup of a a Date (on the Deliverable Tracker, shown in screenshot #1). The lookup will use the Effective Start and End dates to find the appropriate range, then the end result will be the text value column at the far right. My formula shows a blank result, here is the formula:…
-
INDEX / MATCH / CONTAINS Assistance
Looking for your help, the below formula works for the moment: =IFERROR(INDEX({SFDC Opps for Renewals Range 1}, MATCH(SFOP@row , {SFDC Opps for Renewals Range 2}, 0)), "No Match Found") However the column above actually contains multiple SFOPs and so I think I need to add a CONTAIN element to the formula so when SFOP…
-
How to update INDEX formula criteria in a specific way?
My company has created a freelancer invoicing system using two sheets. It depends on an INDEX formula that we now need to improve, which I'm having trouble doing. Sheet #1: A database with information on all individual freelancer (aka subcontractors) contracts (aka SOWs) Sheet #2: A form-based tracker with notifications…
-
Troubleshoot Workflows/notification not going to specific people (index/match formula driven)
Hello! I have a universal intake sheet to manage a variety of request. Assigned approvers are populated based on index/match formulas using a contact column. The notifications are working for everyone except one person. Oddly enough, when I push the notification as a test (and notify me), I don't get the notification…
-
Index / Match
Looking for some guidance. Goal: If "KPI Availability %" @ row is equal to or greater than "Annual Score % Availability" row 10 then it will look at the "Previous Contract Year Event" @ row and match it with the column header 0 , 2,3,4,5,6,7,8 (individual columns). Then it will look down the matched column where it matched…
-
Index/Match formula using a range of values?
Hello, I'd like to create a formula or series of formulas to accomplish the following… There are two sheets: Source Sheet Return Sheet Source Sheet: https://us.v-cdn.net/6031209/uploads/RY816MEYP46R/example-source-sheet.xlsx Return Sheet: https://us.v-cdn.net/6031209/uploads/ZG5YB9OMWQK4/example-return-sheet.xlsx What I'd…
-
Cross-reference sheets to find matches
Hi! I am trying to determine how many of our students are later hired by our institution. I have a Sheet with a Student Name column , and would like to add a "Hired" Column in this sheet that would cross reference to a second sheet (Master employee list) and flag if there is a match in the Employee Name column (so if a…
-
Listing cell contents from multiple cross reference fields in one cell (if statement, index/match?)
Hi, I am trying to do an if/index/match statement to pull the contents of a cross reference cell if it is not blank into a single cell. I need to be able to list several non-blank values with a paragraph break in between in a single cell and I am drawing a blank. Right now I have this: =IF({ParkingLotBlockedComm} <> "",…
-
Help with an INDEX(MATCH(MATCH( Formula
Hey all, I have two sheets: Business Consult and Project Proposal. I'd like to move specific info from that sheet onto the Project Proposal sheet using a unique identifier, the Business Consult ID. I've mirrored columns on both sheets and they are in the same order (though Project Proposal sheet has additional columns). My…
-
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…