Can I dynamically reference the same cell across multiple worksheets in IF and TEXTJOIN?

Therapists Availability
Hello,

I have searched and searched for the formula I need to have a formula (in G2) on my Master sheet reference a specific cell (B2) on several worksheets. I have tried using TEXTJOIN with a nested IF statement (the IF statement in H2 works like I want it to). The TEXTJOIN in H3 appears to work but I don’t have a reference to multiple cells. What I want the chart on the master page to do is show all the people who have availability at a specific time in the cell for that day and time or if no one does then to say none. I would really really like the formula to update when new sheets are added If at all posible.

Monday Tuesday
7:30 Person 1 (w) Person 1 (e/o)
Person 3 (e/o) Person 4 (w)
Person 5 (w)
8:15 none Person 2 (w)
9:00 Person 2 (e/o) none

I cannot run macros in the environment I am working in.

Thanks in advance for any help.

Is consolidating Sheets Person 1,2 and 3 into a single sheet an Option for you?
This would allow you to set your data up in a proper database format resulting in much easier formula including auto-update if you add additional data.

The way it is currently set up may be doable but 3D formulas across multiple sheets + potentially some auto-updating will inevitably be fairly hard to understand/audit and error prone.

Thank you so much for taking the time to respond.

I will have 17 or so sheets as it stands now with others added over time. Combining into one would make it less readily viewable (not having to scroll to locate the correct chart) by others which is a large part of why this project is happening. However, I really don't want something that will break over time. I can look at the combining option if that is my best route.

Originally Posted by arsenalmom

Thank you so much for taking the time to respond.
I will have 17 or so sheets as it stands now with others added over time. Combining into one would make it less readily viewable (not having to scroll to locate the correct chart) by others which is a large part of why this project is happening. However, I really don't want something that will break over time. I can look at the combining option if that is my best route.

I agree with RaulSerg consolidation is the best way to go, particular visualization can be done using a simple drop down menu.

I made a workbook for you with the consolidated concept, now you will see all the 'persons' schedules in one sheet named 'Consolidation' (of course you can change that) where you can select in a Slicer the collaborator that you want to view and add, or change something.

By being so much easy to work that way I did a good work with the summarized situation of the collaborators in the Visualization sheet, hope you like it.

Already a new upgrade, now you can alternate the visualization between person and availability using the cell in N1.

Wow. Thank you so Much DJunqueira!! This looks so nice and clean and I am sure will meet our needs well. I really appreciate your help on this!!

Tks for your replay, I really liked to do this worksheet.