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.

Screenshot-2023-09-05-at-12.10.42-PM.png

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.

Screenshot 2023-09-05 at 12.10.16 PM.png

Thank you so much!

Find more posts tagged with

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.