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
-
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
-
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
-
thank you, @DKazatsky2
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!