I have a few questions about a sample spreadsheet that I've edited for your viewing here: https://docs.google.com/spreadsheets/d/14LSODtDZNTkfaVoB7lBmJKNeCx8ZKwIPnUwdwwgV0x4/. I'll start with the right-most tab and work my way left, but to save time the left-most Distribution tab is where I'm having troubles!
Reward Tables: There are various different rewards/badges (Column A), they'll each be worn/displayed in a specific location (Column B), and they'll each have a specific color (Column C). All of this is something I would enter/update by hand.
Roster: The year/status of a particular member (Column A) and their name (Column B). This is another thing that would be entered and updated by hand.
Reward Logs: This is a copy/paste of a reward log kept up to date by a website. As the site gets updated, we copy and paste two columns from the table where it displays the name of the winner (Column A) and the reward they received (Column B). It's important to note that there could be reward entries in here that don't necessarily have a place on the "Reward Tables" tab since we only care about a particular few that are defined in Reward Tables.
Master List: This tab pulls together everything from the three tabs to the right of it. Column A will IMPORTRANGE() from the Reward Logs tab, thus populating Column A and Column B.
-
- Column C contains the formula
=xlookup(A2,Roster!$B$2:$B$27,Roster!$A$2:$A$27,"?",0,1)to obtain the year/status of the name in Column A.
- Column C contains the formula
-
- Column D uses the formula
=xlookup(B2,'Reward Tables'!$A$2:$A$17,'Reward Tables'!$B$2:$B$17,"N/A",0,1)to obtain the location the reward name in Column B is to be worn.
- Column D uses the formula
-
- Column E uses the formula
=xlookup(B2,'Reward Tables'!$A$2:$A$17,'Reward Tables'!$C$2:$C$17,"N/A")to obtain the color of the badge listed in Column B.
- Column E uses the formula
Distribution: This is the tab that the user(s) would view for the information they need, and it's where my problem is occurring. The main goal is to display all of the names for each year/status into its own table or section, manually list by hand the badges/rewards that are applicable to that year/status, and then display whether those names have that color badge by possessing any number of badges that are that color. I have a comment in cell C4 with more context if necessary.
Pivot tables were working okay enough, but let's say that Name5 didn't obtain any rewards yet at all, and therefore wouldn't have any kind of entry in the Reward Logs tab or the Master List tab, but would have an entry on the roster.
In this case, the Pivot Table would display the names of the Freshman when that filter was added, but because Name5 didn't obtain an award yet, their name was left out completely from that table.
Furthermore, let's say all of Name3, 5, and 14 through 18 (i.e., the Freshmen) had entries for yellow and green, but none of them had entries for red+blue. In this case, despite being present in the filter, red+blue header wouldn't display at all until at least one person received it.
When I moved away from pivot tables I tried to use various combinations/nested COUNTIF() and XLOOKUP() or VLOOKUP(), but I always kept coming back to the trouble of having to reference two variables at once, the name and the color, and I couldn't figure out how to make that happen. For example, B7:D7 shows what I had tried to do (==COUNTIF('Master List'!$A$2:$E$108,xlookup(B2,'Master List'!$E$2:$E$108,'Master List'!$A$2:$A108))), and it did return a number, but it wasn't the correct number :(
Thank you in advance for any kind of help you can send my way! :)
