I'm making a schedule for special guests who are participating in a four day multi-event festival. Each guest will be involved in a different combination of rehearsals and performances at different times and on different days. I have created a multi-page spreadsheet, where the front page is the master schedule for each person and the ensuing sheets are each individual event schedule with all its rehearsals and people.
I have set up the front page to basically just say whether any specific person is free or not in any given time slot (here is an example formula =OR(ISTEXT('Event 1'!B3),ISTEXT('Event 2'!B3), ISTEXT('Event 3'!B3), ISTEXT('Event 4'!B3) etc.) - this leaves me with TRUE meaning they are busy and FALSE meaning they are free at that time.
My questions are the following:
1) Is there a way for me to instead have it generate WHICH sheet is generating the TRUE value, instead of just saying TRUE?
2) Is there a way for it to generate an error message if I've double booked the person, i.e. if more than one of the values is true?
3) If I can't do number 1, is there a way for me to change "True" and "False" to say "Busy" and "Free" in an OR function?
I don't mind totally starting over with a new approach if I can end up with a formula on my master schedule that says, say, at 9am they are busy with event 3, at 10am they are busy with event 5...
I've attached my blank template so you can see how the data WILL be organized, even though there isn't any data in there at the moment, just my example formula.
Any insight is appreciated!
Bookmarks