https://docs.google.com/spreadsheets/d/1Gicj7hiR80NQzwBsn535gFAV4xn1lZvcXP1ch6U23pQ/edit?usp=sharing This is a copy of the Sheet I designed with lots of googling. I've sanitized the customer info and put generics in and erased non-pertinent information to answer my question.
This is for my brothers lawn care business. He's not likely to have more than 5 appts in a single day. His wife does most of the appts and finance stuff so she is the main user of this sheet.
Objective: Make the Calendar tabs(the *Month tabs) show multiple appts for the same day on separate rows within the date's block.
Current Usage: The Calendar tabs show "Next Appt" from the "Work Log" tab using a VLOOKUP. The limitation is that it only shows the top row customer. So if multiple people have appts on the same day, the calendar only show the top row person. You'll see 2 Next Appts on Work Log for Alice and bob on 8-30, but on the *Month tab, Only the top row, Alice, shows up.
What I've tried:
In the demo version you'll see on August 30th the example of current usage. I can't figure out how to modify the current usage to add the additional vlookup condition of the job# and get the customers name to output.
In a sentence description of the formula it would be: I need E28, E29, E30, E31, E32 to show the name of the customers from "Work Log>Next Appt" date if the JOB# of the corresponding F28,F29,F30,F31,F32 to "Work Log>Job # of the Day" and the Date of the corresponding day is E27 all match.
I hope I phrased this correct.
It sounds like what you are looking for is the FILTER command.
In cell C28 I added this formula:
=ARRAY_CONSTRAIN(IFERROR(FILTER('Work Log'!$K$2:$K$997,'Work Log'!$J$2:$J$997=C27)),5,1)
It works like this:
=ARRAY_CONSTRAIN(IFERROR(FILTER(names,dates of appointments = date on calender),5,1)
ARRAY_CONSTRAIN - Limits the number of results from the filter to five rows and one column.
FILTER(HAYSTACK, ARRAY OF BOOLEAN VALUES) - returns haystack where an array of boolean values is true.