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