formula to fill in one sheet based on the dropdown symbol in report from another sheet

Hello,

I need a formula or a suggestion for a better structure.

I have a Report from Sheet 1 and then the Sheet 2. I need to add the names to Sheet 2 based on the symbol drop-down selection in the Report from Sheet 1.


a. Report from Sheet 1: the faculty marks the weeks (in columns) they are not available in this report.

b. Sheet 2 (admin working sheet): I need the Names who are not available and marked with the red cross symbol in the Report from Sheet 1 above to appear in the "Faculty Not Available" column in the corresponding Weeks rows.


Thank you so much!

Best Answer

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @OshaK ,

    You will not be able to evaluate a formula against the report, you will need to reference the underlying sheet.

    Here is one way that works but requires a new formula for each row in sheet 2 (admin working sheet).

    In the "Faculty Not Available" column, use the following formula.

    =JOIN(COLLECT({Name}, {01/02/24 - 01/08/24}, "No"), ", ")

    **NOTE: the range {01/02/24 - 01/08/24} needs to be changed for each row of this sheet

    The output is "Name 1, Name 2, Name 3". This solution can get a bit tedious so hopefully someone else may have a more elegant solution.

    Hope this helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭
    Answer ✓

    Hi @OshaK ,

    You will not be able to evaluate a formula against the report, you will need to reference the underlying sheet.

    Here is one way that works but requires a new formula for each row in sheet 2 (admin working sheet).

    In the "Faculty Not Available" column, use the following formula.

    =JOIN(COLLECT({Name}, {01/02/24 - 01/08/24}, "No"), ", ")

    **NOTE: the range {01/02/24 - 01/08/24} needs to be changed for each row of this sheet

    The output is "Name 1, Name 2, Name 3". This solution can get a bit tedious so hopefully someone else may have a more elegant solution.

    Hope this helps,

    Dave

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!