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 ...
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.
=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.