Help with Link in data

Options

Hi I am trying to link in data from one sheet to another sheet, however I would like to pull the data in column "Creative Backlog" only if it matches the column data in "Team" that is "Creative" what kind of a formula I must give? The following is not working

=INDEX({Copy of Marketing tracker_9 March Range 1}, MATCH("Creative", [Team]:[Team], 0), ([Creative: Backlog]:[Creative: Backlog]))

Answers

  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭
    Options

    Hi @Santiswaroop,


    Your Creative Backlog formula should be adjusted to the following:

    =INDEX({Copy of Marketing tracker_9 March Range 1}, MATCH("Creative", [Team]:[Team], 0))

    If you are trying to match different criteria then you may need to use INDEX/COLLECT.


    Try the above. If it does not work, then please share copies of the column headers for your sheets.


    All the best,

    Sandra

  • Santiswaroop
    Options

    Hi Sandra,

    Thanks for the inputs but the formula working, Let me add more details. I have 2 sheets "Copy of Marketing tracker_9" sheet and "PMO Team Tracker" sheet.

    In the "tracker_9 sheet" I have many columns but the 1st column is named as "Team" which is drop down (single select). I want to link out data from this sheet to "PMO team" sheet all of the columns only if the first column "Team" contains "PMO" as selected item, so that the "PMO team" sheet will display all rows which has "PMO" as selected item

    I need to build in similar lines multiple sheets for "Creative" and others selections. Hope I am clear

    Although I am using the cell linking which is manual process looking to automate this process through formulas if possible

  • Genevieve P.
    Options

    Hi @Santiswaroop

    If you have two criteria (one to Match the values, and another to Filter), then try using INDEX(COLLECT instead of INDEX(MATCH.

    Here are some other Community posts that explain this type of formula:

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Sandra Guzman
    Sandra Guzman ✭✭✭✭✭✭
    Options

    @Santiswaroop,


    If you are trying to pull in rows to a unique sheet based on each of the categories available from the list of values from your Marketing Tracker Sheet, then you might want to consider reports as an option instead of using formulas. It sounds like you want the rows for each of the items that is related to a specific team in column 1 to pull over to a sheet. If that is the case, and you are not trying to matching multiple criteria, then I would strongly suggest using the report. Those are bi-direction and you can pull through all columns and simply use the report wizard to specify the criteria to only pull through all the rows that have "PMO" into the PMO report. Then follow the same rules for the other reports.

    All the best,

    Sandra

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!