Formula to find date in row = current date and then cal.

[dbmathis]

Hi,

I have a question for the formula gurus of the forum.

I have the following on my sheet.

Cash Flow (Oct-2005 - Oct-2006).xls
	G	H	I	J	K	L	M	N	O	P	Q	R
129	$977.07	$161.18	$635.29	$2,059.40	$2,533.51	$3,007.62	$3,481.73	$3,955.84	$4,429.95	$5,854.06	$6,328.17	$6,802.28
130	foobared
131	Oct-05	Nov-05	Dec-05	Jan-06	Feb-06	Mar-06	Apr-06	May-06	Jun-06	Jul-06	Aug-06	Sep-06
Cash Flow Statement

How would I construct a formula that would look at row G131:R131 and find the date (month) that matches the current date (month). Then become the value of the cell that is 2 cells above the cell that it found which is the current month?

For example: today the formula would be = 977.07. The formula would see that it is October and choose cell G131 because it is currently Oct and then look 2 cells up and find 977.07.

Thanks

 

[Von Pookie]

I doubt it's perfect, but this seems to be working for me:

=SUMPRODUCT(--(MONTH($G$131:$R$131)=MONTH(TODAY())),--(YEAR($G$131:$R$131)=YEAR(TODAY())),$G$129:$R$129)

 

[barry houdini]

Try

=LOOKUP(MONTH(TODAY()),MONTH(G131:R131),G129:R129)

 

[dbmathis]

Both of those work initially, but if I rearrange the row and move the moths around the formula doesn't update.

Is this normal? Is there a way to make it update if I re-arrange row G131:R131?

Thanks

 

[Von Pookie]

I can't speak for Barry, but the formula I posted updates correctly when I try it.

Did you perhaps get calculation changed to "manual" instead of "automatic?"

 

[dbmathis]

I have it set to automatic.

 

[dbmathis]

This makes no sense! October was in cell R131 and the formula yielded a value of $6,802.28. I moved October to cell G131 and the formula still says $6,802.28?

I even closed out the workbook and reopened it and the value is still $6,802.28. Should the value not be $977.07 if October is sitting in cell G131?

I am clueless.

 

[barry houdini]

LOOKUP only works when your lookup range (the months in your case) are in ascending order.

If they're not going to be in order (and assuming your dates are the first of each month formatted as "mmm-yy")

=INDEX(G129:R129,MATCH(TODAY()-DAY(TODAY())+1,G131:R131,0))

 

[dbmathis]

Barry,

I use the format that you specified.

That worked initially, when I had October in cell G131. I got a value of $977.07.

Then I moved (rearranged the months and put October last in cell R131 and the formula spit out #N/A.

Any ideas?

 

[barry houdini]

Did you see my last post?

What is start equal to?