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

If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in
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 text
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 will
make it into.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175

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"))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:


If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in
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 text
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 will
make it into.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175

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

"JBeaucaire" wrote:

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"))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:


If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in
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 text
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 will
make it into.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175

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?

"JBeaucaire" wrote:

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"))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:


If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in
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 text
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 will
make it into.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=62175

see response to your later posting at 6:55

--
Regards
Roger Govier

"Joe" wrote in message
...
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?

"JBeaucaire" wrote:

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"))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:


If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in
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 text
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 will
make it into.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile:
http://www.thecodecage.com/forumz/member.php?userid=73
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=62175

Thanks very much Roger. You help is much appreciated.

"Roger Govier" wrote:

see response to your later posting at 6:55

--
Regards
Roger Govier

"Joe" wrote in message
...
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?

"JBeaucaire" wrote:

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"))


--
"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.


"JBeaucaire" wrote:


If row 1 has TEXT values Jan-Dec in A1-L1, then Jan-Dec again in
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 text
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 will
make it into.


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile:
http://www.thecodecage.com/forumz/member.php?userid=73
View this thread:
http://www.thecodecage.com/forumz/sh...ad.php?t=62175