Forum Discussion
formula for scheduling help
I am creating a master list of people, with criteria for the various types of work and time slots they have agreed to for volunteering. I created a simple way to visually see where people have been slotted in, but would now like to:
1) Turn this into a printable, easy to view format using some sort of formula or filter.
2) Find out if there is any easier way to automate this using some rules?
I've included three screenshots - the bottom one is the Master List where I've input the schedule using a color code with one or two (for the number of people scheduled to each color coordinated class). The top and middle images are what I would like the printable file to look like. Just not sure how to use IF or VLOOKUP or some other formula to make it all work!
Thank you for any assistance!
1 Reply
- The basic answer is yes you can. I wish you would provide a sample sheet (no private info please and if those are real names above that should be redacted for their privacy). That said there are a number of improvements to the master table and other tables that could make the formula and process easier including making headers and label consistent and making the master table 'Formatted as a Table'. I will also make a few assumption including, the master table is on a sheet called 'Master', the header cell 'First Name' is cell B3, there is a 'Last Name' column that is hidden (thank you for doing that) and in C3, and the assignment range is I4:Z100, the upper left corner of the first table with Tamura's name I will assume is cell B4 and that the header has cells that are merged sometimes for the date and the 'hidden' cell is empty. the formula would be something like:
=XLOOKUP(
IF(LEFT($A4,5)="CHECK", "Yellow", IF($A4="", TEXTBEFORE($A3," CLASS")&" 2", TEXTBEFORE($A4," CLASS")&" 1")),
INDEX(Master!$I$4:$Z$100,0,XMATCH(IF(B$2="",A$2,B$2),Master!$I$2:$Z$2)+(B$2="")),
Master!$B$4:$B$100&" "&Master!$C$4:$C$100,
"")
As you can see it is a basic XLOOKUP with a lot of conditions inside to deal with your headers and offsets and such.
The first line is creating what to look for.
second line is pulling the correct column in the master table
the 3rd line is creating a column of first name - last name to output
even if this doesn't quite work I hope it puts you on the right track for that top table and hopefully the next table too.
If you don't know already, the '$' character is put before a reference range you don't want to change when you copy or fill. So I 'locked' row 2 in some cases or column A or in the case of the table ranges both the columns and rows.
