Originally Posted by Chris
Ok, here;s my current excel problem, then, since all ya'll are smartasses.
I need to make Excel do two things.
1.) Take a month-end date, and make it return the first month after the previous quarter end. I.e - on 1/31/2011, you return 1/31/2011. 2/28/2011 you return 1/31/2011, 3/31/2011 you return 1/31/2011, and 4/30/2011 you return 4/30/2011.
2.) Take a date/value table like so:
Date 1 Value 1
Date 2 Value 2
Date 3 Value 3
Date 4 Value 4
Date 5 Value 5
...and have excel take a specified range - values between Date 1 and Date 3 - and then sum values 1 thru 3. Assume I have no problem getting excel to figure out what the range is - basically, I just need to be able to specify a sum() range using lookups to specify the start and end date, but return the cell next to it.
For the first question, I managed to get as far as (with a horribly long sequence of month() edate() if() and and() statements, tyo return the right number of the month. I.e - 3/31/2011 would return 1, 4/30/2011 would return 3 - but I didn't get any farther. And, to the best of my knowledge, excel doesn't do anything like the second - find a value, return it's cell address, and use an indirect lookup to give the cell address next to it for inclusion in another formula.