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

Options

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!

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    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])


  • ERinaldi
    Options

    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
    Eric Law ✭✭✭✭✭✭
    Options

    @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
    Options

    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.



  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    @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
    Options

    @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
    Eric Law ✭✭✭✭✭✭
    Options

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!