Specify a column by the month to calculate it's data

[Joe]

I have 12 columns (Jan-Dec) for 2007 and then 12 more columns (Jan-Dec) for
2008.

At the end of each years section of monthly columns I have a Month to Date
Difference column that compares the accumulated months for 2007 to the same
number of months for 2008.

I need a way for the formula in the MTD column to know what month I want it
to calculate to. If I am in Feb I only want it to use the 1st 2 months for
calculations by checking on the date or month I enter at the top of the MTD
column. Currently I edit the formulas so it only includes the months columns
I want. I would like this to be automatic.

A simplified example with just 3 months is shown below: I want the formula
to check the month I type into cell G1 and calulate up to the month that
matches what I typed in. The 1st example shows a 2 in G4 because that is the
diff between Jan & Feb 2007 and Jan & Feb 2008. The second example shows 3 in
G4 because that is the Diff between Jan, Feb, Mar 2007 and Jan, Feb Mar 2008:

A B C D E F G
1 Feb
2 2007 2007 2007 2008 2008 2008 MTD
3 Jan Feb Mar Jan Feb Mar DIFF
4 2 2 2 3 3 3 2


A B C D E F G
1 Mar
2 2007 2007 2007 2008 2008 2008 MTD
3 Jan Feb Mar Jan Feb Mar DIFF
4 2 2 2 3 3 3 3

Thank you

 

[JBeaucaire]

If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again i
M1-X1...
...and values below in row 2 for each month noted above.

For now, place a text entry like Mar in A4.

Now this INDIRECT formula will compare the two ranges based on the tex
entry "Mar"

=SUM(INDIRECT("A2:"&CHOOSE(B4,"A","B","C","D","E","F",
"G","H","I","J","K","L")&"2"))-SUM(INDIRECT("M2:"&CHOOSE(B4,
"M","N","O","P","Q","R","S","T","U","V","W","X")&"2"))

Be sure to copy that as a single row, not the 3 rows the forum wil
make it into

 

[JBeaucaire]

Sorry, I used an intermediate helper cell in B4 to convert the "Mar" to a 3,
here is the long formula with that operation buried in the formula itself.
Sorry for the omission.

=SUM(INDIRECT("A2:"&CHOOSE(MATCH(A4,A1:L1,0),
"A","B","C","D","E","F","G","H","I","J","K","L")&" 2"))-
SUM(INDIRECT("M2:"&CHOOSE(MATCH(A4,A1:L1,0),
"M","N","O","P","Q","R","S","T","U","V","W","X")&" 2"))

 

[Joe]

I get a #REF! error. Does it matter what cell this formula goes in?

 

[Joe]

I found a couple of spaces next to the "2". Now it is working. How do I
implement this in my example to get the Month to Date Difference?

 

[Roger Govier]

see response to your later posting at 6:55

 

[Joe]

Thanks very much Roger. You help is much appreciated.