How to pull data from another sheet based on a specific criteria

ERinaldi

Hello,

I have one sheet that includes all of our projects listed in rows and I need to create specific sheets outlining the tasks for each of the rows. The new sheet should also include specific data (such as dates, names, etc.) from the first sheet. I'd like to be able to link data from specific cells from that first sheet to the new sheet automatically when the name of the new sheet is changed to the title of the task in the original sheet. For example, if the new sheet includes the title of row 7 from the original sheet, cells in the new sheet will be formatted to automatically populate data from row 7 where the details of that task are included. Is this possible?

Thanks!

Eric Law

Hello @ERinaldi, screenshots with examples will help a lot with these questions, but what you can do is use and Index-Match function.

=INDEX([Range of data to be displayed from], MATCH([Identifier], [Range to look for Identifier], [sort option]), [optional column index])

https://community.smartsheet.com/discussion/84774/index-and-match-across-two-sheets-a-detailed-explanation

ERinaldi

Hi @Eric Law,

Thanks so much for your response. I'm still quite new to working with Smartsheet. I've found that I can enter formulas into the sheet summary that help pull data. I am still having difficulties with selecting the right data, however. For example, the below formula (that someone else wrote) pulls the correct data for one cell, but I can't figure out how to get data from a cell five columns down in the same row.

=INDEX(COLLECT({International Programs (complete) Range 4}, {Program name}, [Program Name]#, {International Programs (complete) Range 2}, [Calendar Year]#, {International Programs (complete) Range 3}, Term#), 1)

How can adapt this to change the column as needed?

Eric Law

@ERinaldi So an index will only ever take 1 cell information based off of what you are referencing. Typically, when you are doing a index/match, you use an array to an array. A sheet summary field should be used with formulas that analyze the whole sheet, instead of just 1 cell, such as sums, counts, etc.

If you add some screenshots, it will be more helpful.

ERinaldi

Hi @Eric Law,

Okay, thanks! I've attached a few screenshots. I think the sheet summary is the easiest place to add the information; the formula for the data "faculty lead" pulls correctly, but I can't seem to get the formula correct for "Faculty Lead 2" or the dates. The second screenshot is from the original list. Each row is a different project and I want to pull data from some of the columns for an individual row, but not all of them. Once I enter the "Program Title" into the sheet summary of the second sheet, the sections with the correct formula are pulled from the matching row in the original list.

Screen Shot 2023-09-12 at 8.58.31 AM.png

Screen Shot 2023-09-12 at 9.06.16 AM.png

Eric Law

@ERinaldi I don't know what your formulas are, so I can really tell what is happening. I'm just guessing that you need to update your range for the index since Facilities #2 is a different column.

ERinaldi

@Eric Law Ah, okay, yes, that is where I am having difficulty. The formula for "faculty lead" is:

=INDEX(COLLECT({International Programs (complete) Range 4}, {Program name}, [Program Name]#, {International Programs (complete) Range 2}, [Calendar Year]#, {International Programs (complete) Range 3}, Term#), 1)

How can I expand the range to collect data from the "Faculty Lead 2" and date columns? There are five columns between "Faculty Lead" and "Faculty Lead 2." The start date and end date columns are further left in the row.

Eric Law

@ERinaldi So, unlike a vlookup, index will always look at your first column of the range. You will need to create a new range for the index and it should work.