Returning a value from a discontinuous range (a formatted calendar with data) for a specific date

Rapid_Ron_L 

If for one month only return the list for given date

with newly introduced LET() function

=LET(
   MonthRange,$B$6:$H$77,
   AllDays,INDEX(MonthRange,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),
   DatePosition,XMATCH($O$3,AllDays,0),
   DateRow,(INT(DatePosition/7))*12+1,
   DateColumn,MOD(DatePosition-1,7)+1,
   DateValues,INDEX(MonthRange,DateRow+1,DateColumn):INDEX(MonthRange,DateRow+10,DateColumn),
   DateValuesFiltered,IFERROR(FILTER(DateValues,DateValues<>0),""),
   DateValuesFiltered
)

without it by with dynamic arrays

=IFERROR(
   FILTER(
      INDEX($B$6:$H$77,
         (INT(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)/7))*12+1+1,
         MOD(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)-1,7)+1):
      INDEX($B$6:$H$77,
         (INT(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)/7))*12+1+10,
         MOD(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)-1,7)+1),
      INDEX($B$6:$H$77,
         (INT(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)/7))*12+1+1,
         MOD(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)-1,7)+1):
      INDEX($B$6:$H$77,
         (INT(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)/7))*12+1+10,
         MOD(XMATCH($O$3,INDEX($B$6:$H$77,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1),0)-1,7)+1)<>0
   ),
"")

Rapid_Ron_L 

well, I see the file and have opened it. But can't really discern from your description what it is you want to do with this. For example, are you wanting to do proactive, forward looking scheduling, or retroactive counting of days worked per employee?

Nor do I see any named range called "Date Range" in among the named ranges.

That said, let me go back to my first comment (in which I agreed with your assessment) that it's unfortunate that the calendars are all on separate pages. You pointed out that this is "for user best interaction" which is what I expected (or something along those lines).... as well as being concerned that you'd already put so much time into it  The latter, of course, might be countered by "It's now going to take even more time to make it work because of that layout." And the former....well, I would contend that user interaction can be adaptable.

The fundamental point being that although maybe this could be made to work, you're ALREADY finding out part of the difficulty with creating "input sheets" that are user-friendly by virtue of being laid out in a familiar (calendar) manner....that difficulty being that it becomes unwieldy to then extract the data in a simple way. Excel is excellent at extracting data and summarizing it ( via Pivot Tables, etc.) when that "raw" input data are organized in a Table. And, believe it or not, Tables can actually become user-friendly; or perhaps I should say, users can develop a fondness for Tables once they get acquainted with them.

It's possible that somebody else here can help you set this up so that it makes use of the Input Form capability of Excel--I've not ever used that capability, but always developed Tables that can then become the basis for planning team member responsibilities, ensuring coverage for various tasks, summarizing upcoming events, etc.

I do think that anybody wanting to help, though, is going to need a bit more of a definition of how the data in each day is to be used. What you said in your first posting was "This fetches the manpower count from the calendar to use against the needs for each day" making it sound as if you are forward looking. In the spreadsheet itself, however, the legend in the yellow box with the names reads "enter all employee names below one per line then copy and paste all to each day, then individually delete absentees from each day" making it sound as if it is looking backwards (i.e., they haven't been absent until it's happened, even if it was just "today", so it's more of a tracker for actual experience rather than a projection of staffing against need. Which is it?

But that aside, I just see this as not having the flexibility to handle the inevitable staff changes mid-year, whereas a "simple" well designed Table for input, with a Pivot Table for output (to report how many days by month each employee worked, for example), would inherently and easily handle matters of turnover, new hires, and so on.

So I don't mean to give you a hard time; just to suggest that you give some more thought to re-thinking how you've laid it out.

And I will be happy for you if somebody else comes along and helps you solve your problem with far less re-design. Truly.