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

OshaK

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!

DKazatsky2

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

DKazatsky2

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

OshaK

thank you, @DKazatsky2