Forum Discussion
Returning a value from a discontinuous range (a formatted calendar with data) for a specific date
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.
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...
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.
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.
