Finding a cell reference that contains specific value in a range

A workbook with sample data would go a long way.

Something like this then? The elegance factor could possibly be improved using SUMIFS but I'm not on speaking terms with that function.

Square dance in a spreadsheet doesn't sound that bad when you think about it...
I'm a bit curious (and impressed) of your concoction since I doubt that I could solve the problem using the functions you mention.

By the way, I forgot to mention that my formula is an array formula, you have to enter it with Ctrl + Shift + Enter.
Did it solve your problem by the way? Or did I misinterpretate the task? Now that I think about it, it does seem a bit strange...

You saved me quite a bit of embarrassment. Along the way, toying with different versions of this and modifying other areas, I made the mistake of carrying forward a version without this modification in it. Dang!

OK, the excerpt is attached. You want to look at cell ND9. As far as the upper row with dates is concerned, I will be modifying it to reflect whatever year is current but keep the lineage from Jan 1 through Dec 31 stable, with then the only contortion to be displaying and modifications concerning accounting for leap year.

If I have done this correctly we should have a file called xlxtract.xlsm available!

Dang. Have to chase down a minor problem and see if it also manifests itself in other areas. I see that if the day immediately prior to today's date has an absence it does not get tallied. Probably will also hit the current date, and will have to check the other extremes...

Good one indeed! A totally different approach that I think could work but at the moment it does not work I'm afraid. Or at least not the way I understood the task. I modified your formula and put the Today function in an external cell. Then I put a random date function in that cell. Hit F9 to generate a new random date. It should equal 180 regardless of date, shouldn't it?

I get a #Value error on the NC12 cell. Running MSO2010, we have both functions, though not hgihgly familiar with them other than it seems obvious what they do. Thoughts?

Oh... had to change "01.01.2013" to "01/01/2013"... whoops...

Must be in my section on when we have less than 180 days from today back to the first of the year. As I am definitely OC when it comes to these puzzles, I will first figure out where I blew it, correct it, then give myself the gift of your solution!

Ok, I got your solution sorted as well. Shall I present the solution and disrupt your OC session so that you can focus on x-mas stress or would you rather want to carry on OC and forget about x-mas?

Four of them?! Wow. Cell phone charging has a real impact on the electrical bill in your home, eh?

Turns out my "corrected" version of your version isn't 100%. 29 and 30 of june does not work. I put a spin button in there so one can click through those critical dates and see how it works (or rather how it doesn't work). It's a matter of playing around with the >= and the > and the 180 and 179 to make it work.
As you can see your original setup was pretty close, it was just a matter of adding the 180-.

Edit: Replacing the first >= with just > seems to reduce the error. Now there is only 1 day with 179 left to correct.

A perfect imperfection?
You are welcome!

If you look around this forum you will see that the array-formula type of solutions are very common. In fact, before I joined this forum I knew about array formulas but I had almost no experience of putting them to use. After a couple of hundred posts I was up to speed. A solution to a problem like yours does not come from scratch for me. I usually recognize that some parts of the problem can be solved with chunks of array formulas that I have used many times before. Basically I have a little library of array formula blocks in my head to choose from. Starting with these blocks I then figure out what functions I need to fill the gaps and make it all work. The dayno-of-the-year formula I could have figured out myself but I figured that an optimal one is only a google search away so I borrowed that from somewhere. If you look in the formula the dayno-of-the-year formula is used 5 times within the whole formula, both in array and non-array form.
The Evaluate formula button (under the formulas tab) is a very useful tool when it comes to array formulas.

Again, you are welcome!

I am trying to formulate how to convert the chart over to generic figures across the top line that had the dates of Jan 1 through Dec 31 so it will be for any year and then accommodate for leap year - the idea is that this is to be used for every year, not just 01/01/2013 through 12/31/2013. I am starting to see how it all works, but if I go from a table of dates to a table of text values I am wondering if I best instead of text values substitute a table of dates based on =DATE(YEAR(TODAY()),1,1), <previous cell> + 1, <previous cell> +1, ...
Thoughts?

Naw, that won't work for all the year will be converted to the new year once the next year comes about, but something to this effect should work...

I strongly suspect the solution to a perpetual circular calendar in linear form that transitions to the next year when the displayed date would be 180 days or more less than TODAY() is with an array formula, but so far my mind can't wrap around it...

I am posting what I have come up with as the final piece of this puzzle for the calendar portion, and I hope it will fit into the accumulator equation graciously created for me by Jacc. Either of the two lines highlighted in yellow can work, but I still suspect there is a more elegant way to do this, probably with an array formula. If so, I sure would appreciate the logic in how to progress to a solution for I need to start thinking that way to learn and at some point teach others instead of begging for answers!


As testimony to Jacc's accumulator, it seems to be handling anything weird I throw at it!

Originally Posted by brucemc777

As testimony to Jacc's accumulator, it seems to be handling anything weird I throw at it!

Good to hear it works! I credit that to using day-no-of-the-year in my function a lot. That way different years and stuff don't confuse it.

My posting in this thread has been interrupted for a while now, not only by x-mas but by work of all things! That means that I'm not really here now...
Nevertheless, since word got around that you now got your arms around the array formulas I think it's time to mention the Evaluate Formula button that is located under the Formulas tab. The window is ridiculously small so in order to get something out of it I sometimes create a scaled down test version of a formula and run it there. It gives excellent insight in how array formulas work. It will also give you the opportunity to be confused on a whole new level, the inner workings of Excel is sometimes so obscure that you wish you never knew.

I have yet to open one of your files and it will not happen tonight. I'm guessing weekend...

Mailbox is full when it hits 70(!). Wasn't there a guy once that thought that no one would ever need more than 64k memory?

By the way, do NOT download, open and run this spreadsheet. http://spreadsheetpage.com/index.php...mula_calendar/

That SUMIFS formula is looking pretty darn good, your OC is progressing nicely! Your named formula is also pretty cool.

The weekday you can get from the date. For instance in C3 type =C4 than format C3, choose Custom and type dddd but I would be surprised if you didn't already know that.