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

[Rapid_Ron_L]

Using the date in the cell above this formula I need to find the value in a cell 11 rows below the date in a formatted calendar. The calendar is lain out in multiple rows of dates, arranged by week (from a calendar template I borrowed). I've assigned the name "Date_Range" to the discontinuous range of dates on the calendar. I've fiddled around with Index-Match formulas with no success ...

Can anyone throw me a life preserver?  :)

p.s. This fetches the manpower count from the calendar to use against the needs for each day. To complicate matters, the calendar is in a different file and each month of the calendar is on a different sheet. I know how to use external file references and indirect to get to the the file name and sheet.

[#2]

@Rapid_Ron_L 

Is it at all possible for you to upload the actual files (after anonomysing any names). Your description is reasonably good, but you'd still be lucky if anybody could create a functioning formula just from that. The actual spreadsheet(s) would also be better than images....

P.S. you're certainly right that having each month's calendar on a separate sheet is a major complicating factor. Are you in a position to make major changes to how the data are laid out? That kind of thing--although it makes for a nicer and more user-friendly display--makes it a LOT harder to use many of Excel's wonderful functions, which can extract data in all kinds of neat ways from a well-designed database. So if you're in a position to modify the way this is designed in the first place, we may be able to make some really effective changes.

[#3]

Hmmmm, I attached a file, or so I thought. I'll try again here - done, but I don't see it ....

I don't want to change the calendar layout, for user best interaction, plus the time I have into it.  As I mentioned, I can already "build" the sheet reference from my input date, so getting to the correct page is fine. Just need to know how to search thru the range.  

Right now I'm experimenting with row and column generation, using help from here: https://exceljet.net/formula/get-location-of-value-in-2d-array .  Having some success, but a bit cumbersome. Still...

[#4]

@Rapid_Ron_L 

The file should attach if you drag it to the area below the purple bar, where you enter your own message. See below for an image from this message I'm typing.

[#5]

I did that, and also did it via browser. Maybe macro files can't be attached.  I'll save as .xlsx, as I don't think there are any macros!  I think that worked.

[#6]

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

[#7]

@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
   ),
"")

[#8]

@Sergei Baklan 

For the sake of the rest of us (well, maybe just me), Sergei, could you explain those two formulas, and how they're working, what they do? I don't know how you come up with them for something like this.

[#9]

Well, I've got it all working! Even with separate tabbed calendar sheets and different calendar files for each year. I'm pressed for time right now, so I'll evaluate the above feedback and report on how I did it in a bit. Thanks all, and forgive my non-immediate follow-up (to come).

[#10]

@mathetes 

The core of the formula is

INDEX(MonthRange,(INT(SEQUENCE(6*7,,7)/7)-1)*12+1,MOD(SEQUENCE(6*7)-1,7)+1)

which returns all dates in the month as 1D array.

Let say we have such range and would like to return all values in yellow sequentially:

The rest is just arithmetic. Finding relative position of the value in such array (e.g. 5 for a15) and knowing number of cells in each row and step between row with MOD and INT we find position of this value in source range (5th row, second column). INDEX(range,5,2+1):INDEX(range,5,2+1+3) returns values for 3 cells under found one. Filter result if necessary.

All above steps much easier to code with LET(). If not available, with named ranges and formulas. Or straightforward code as in second formula.