Returning cell based on 2 criteria in another sheet

Hi,

I'm back! I am trying to pull the course cell per semester based on a student's ID and also on the Term (fall or spring) columns.

This sheet is my "eligibility sheet" where I would like the course from my Active TAships sheet to go

Active TAships Sheet:

The formula I am using in the FA Acceptance (also will be in Sp Acceptance column) is: =INDEX({ActiveCourse}, MATCH([CUID (7 digits)]@row, ({ActiveID}), 0))

While this does populate on the Eligibility Sheet the FA Acceptance, it duplicates it for the SP Acceptance…I'm sure it has something to do with the Term field, but I cannot get anything but errors.

Any ideas? Thank you!

Lori

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @maineL

    Thank you for an interesting real-world example.

    Here is a solution using the JOIN(COLLECT()) functions.

    Formula for Fall (FA Acceptance)

    To retrieve courses for the fall semester, use this formula:

    =JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "F"), CHAR(10))

    Formula for Spring (SP Acceptance)

    To retrieve courses for the spring semester, use this formula:

    =JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "S"), CHAR(10))

    https://app.smartsheet.com/b/publish?EQBCT=321fba69853e4b59802200b6b41a429c

    Explanation of Each Part:

    • {ActiveCourse}: This is the range containing the course names in the Active TAships sheet.
    • {ActiveID}: This is the range containing CUIDs in the Active TAships sheet, matching the CUID in your eligibility sheet.
    • VALUE(@cell) = VALUE([CUID (7 digits)]@row): This ensures that the CUID values in both sheets are treated as numeric values. This step prevents mismatches due to any formatting differences (e.g., leading zeros or text format).
    • {ActiveTerm}: This range contains the term information ("F" for fall and "S" for spring) in the Active TAships sheet.
    • "F" or "S": This specifies the term filter, pulling data for either fall ("F") or spring ("S").
    • JOIN(..., CHAR(10)): Combines any matching courses into a single cell, with each course on a new line (using CHAR(10) to add line breaks).

    Usage:

    • FA Acceptance Column: Use the formula with "F" to pull only fall courses.
    • SP Acceptance Column: Use the formula with "S" to pull only spring courses.

    This approach should now give the correct, term-specific course list without duplicates. Let me know if there’s anything else I can assist with!

Answers

  • maineL
    maineL ✭✭✭

    I also tried this formula: =MAX(COLLECT(Lookup File - {ActiveCourse}, {Lookup File - {ActiveID}, =[CUID (7 digits)]@row,{ActiveTerm},0) and get unparseable.

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    Answer ✓

    Hi @maineL

    Thank you for an interesting real-world example.

    Here is a solution using the JOIN(COLLECT()) functions.

    Formula for Fall (FA Acceptance)

    To retrieve courses for the fall semester, use this formula:

    =JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "F"), CHAR(10))

    Formula for Spring (SP Acceptance)

    To retrieve courses for the spring semester, use this formula:

    =JOIN(COLLECT({ActiveCourse}, {ActiveID}, VALUE(@cell) = VALUE([CUID (7 digits)]@row), {ActiveTerm}, "S"), CHAR(10))

    https://app.smartsheet.com/b/publish?EQBCT=321fba69853e4b59802200b6b41a429c

    Explanation of Each Part:

    • {ActiveCourse}: This is the range containing the course names in the Active TAships sheet.
    • {ActiveID}: This is the range containing CUIDs in the Active TAships sheet, matching the CUID in your eligibility sheet.
    • VALUE(@cell) = VALUE([CUID (7 digits)]@row): This ensures that the CUID values in both sheets are treated as numeric values. This step prevents mismatches due to any formatting differences (e.g., leading zeros or text format).
    • {ActiveTerm}: This range contains the term information ("F" for fall and "S" for spring) in the Active TAships sheet.
    • "F" or "S": This specifies the term filter, pulling data for either fall ("F") or spring ("S").
    • JOIN(..., CHAR(10)): Combines any matching courses into a single cell, with each course on a new line (using CHAR(10) to add line breaks).

    Usage:

    • FA Acceptance Column: Use the formula with "F" to pull only fall courses.
    • SP Acceptance Column: Use the formula with "S" to pull only spring courses.

    This approach should now give the correct, term-specific course list without duplicates. Let me know if there’s anything else I can assist with!

  • maineL
    maineL ✭✭✭

    You are AMAZING!!!! Thank you so much! I've been pulling my hair out!!!! This worked perfectly! THANK YOU!!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!