-
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…
-
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: Return Sheet: What I'd like to happen is… on the Return Sheet: When a value is entered in the Constituent Number column, the sheet returns a corresponding value in…
-
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…
-
[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…
-
Lookup on a Many to one sheet
Okay, this may be a bit tricky, so I would appreciate your help. I will be creating a new sheet, and accessing both of the sheets below. Let's call the screenshot at the top, SheetA, and the one at the bottom SheetB. Now, SheetA is a "one to one" with the TDLs listed in the column on the right, there is only one entry on…
-
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…
-
How do I use Cell reference in a formula when using Index?
I have a simple table that has the following columns (along with other columns), ZZZ YYY 10 15 and I would like to replace the ZZZ and YYY in the following formula =INDEX([Thing1]ZZZ:[Thing2]YYY, 1, 1) With the data from the columns above so that the resulting formula would be: =INDEX([Thing1]10:[Thing2]15, 1, 1)
-
Contact Field in Index Match Returning Strange Values
I have a project plan template that is created through the control center. The owner of certain tasks is dependent on what department the project is in. I have a background sheet that is a task owner crosswalk, and then on the project plan it will run an index / match to pull the correct owner. This has been working fine…
-
Fix #INVALID COLUMN VALUE error when using Index Match in a different column type than source sheet
Hello! I am curious if there is a solve to the #INVALID COLUMN VALUE error I get when trying to pull a value from another sheet using Index Match, and both the source sheet and current sheet have different column types. I am currently using the following formula: =IFERROR(INDEX({OChM - Intake - Intake Meeting Date},…
-
Help With Index-Collect and Cross Sheet Referencing
Hi all, I am attempting to return a Contractor's name from a source sheet using multiple criteria (if they are subcontracting and what their scope of work is). The issue I am running into is the unique ID I use to connect the sheets (The PCM Number) has duplicate values when there are subcontractors (multiple contractors…
-
IF formula for INDEX/MATCH across sheets
Hi, I'm really struggling with this one. I have a sheet that has a drop-down with options for "Yes" and "No" and I want to perform an INDEX/MATCH function based on another cell in that row, but only if the result of the drop-down in that row is Yes. The sheet reference for the Yes/No drop down is {App_S}, and the two cells…
-
Using multi-select dropdown list as criteria for Index/Match Lookup with SUMIFS
Hi all — Looking for some guidance on the below… I have a sheet named "Tracker" with a dropdown column of job levels (named "Target Audience") and another column that I am wanting to sum the job levels from whatever is selected for that row ("Total Target"). The totals for each job level are pulling from as a cross sheet…
-
Help with Join+Collect Formula
I need to reference another sheet to join and collect the skills of employees. The yellow is my desired outcome, and I'm using the following formula. Formula: =JOIN(COLLECT({Detailed Resource Data Master Range Skills}, {Detailed Resource Data Master Range Emp ID}, [Employee ID@row), ",") I'm not getting any errors, the…
-
Concatenating multiple fields from SourceSheet into one field in DestSheet
Hello all, I am new to Smartsheet and this community, so I apologize in advance if my question has been asked and answered numerous times already. I just couldn't find my answer. Here it is anyway: I am trying to concatenate all fields I find in "Note" on "SourceSheet" and place the conatenated string of text in…
-
Update a referenced sheet without individual column formulas
HI, I have a master sheet that feeds data into a helper sheet. I want the helper sheet to automatically update whenever the master sheet data changes. Is there a way to sync all columns based on a unique row ID (to avoid pulling in the duplication of rows), without having to write individual formulas for each column? I've…
-
Update a referenced sheet without individual column formulas
HI, I have a master sheet that feeds data into a helper sheet. I want the helper sheet to automatically update whenever the master sheet data changes. Is there a way to sync all columns based on a unique row ID (to avoid pulling in the duplication of rows), without having to write individual formulas for each column? I've…
-
Index/Match using dropdown with multiple selections
Hi, I'm trying to use Index/Match to list the mission statement(s) for each selected non-profit from a dropdown that allows multiple selection. The mission statement lives on a reference sheet and I'm able to get the mission statement if only one non- profit is selected but not when multiple is selected. I thought I found…
-
Formula to populate column with value from another column
Hello, I have a helper column (Project Title) that is supposed to populate with the Project Name based on the below formula. The AI indicates that the formula is supposed to locate the cell in the column "Action Items" that contains the term "Project Name" and then populates the "Project Title" column with whatever is…