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

Question

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.

mathetes · Answer

Rapid_Ron_L&nbsp;&nbsp;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....&nbsp;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.

rapid_ron_l · Answer

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

mathetes · Answer

Rapid_Ron_L&nbsp;&nbsp;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.&nbsp;

rapid_ron_l · Answer

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

mathetes · Answer

Rapid_Ron_L&nbsp;&nbsp;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?&nbsp;Nor do I see any named range called "Date Range" in among the named ranges.&nbsp;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&nbsp; 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.&nbsp;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.&nbsp;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.&nbsp;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?&nbsp;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.&nbsp;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.&nbsp;And I will be happy for you if somebody else comes along and helps you solve your problem with far less re-design. Truly.

Forum Discussion

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

9 Replies

Resources