Array Formula Problem
 

Hey all, long time reader, first time poster.

I've written an statistical program for totaling results for my
wrestling team/individuals. I use an array to sort by name to detail
that persons stats.

I originally enter the data using a Macro to move the data from an
entry form to the data sheet. When hitting "submit" after entering the
stats, I have to add a row to the data sheet so the Macro has a place to
put the data entered. That works fine.

Problem happens in the array when the add row occurs... my formula also
gets moved up by one row. I want the data to remain the same.

Let me try explaining with the formula. First line is what the array
looks like before hitting submit. Second line is what happens after
the data has been entered. I don't know how to LOCK this in.

=SUM(((Wrestlers_Statistics!$B$2:$B$64998)=$A$3)*( Wrestlers_Statistics!$M$2:$M$64998))
=SUM(((Wrestlers_Statistics!$B$3:$B$64998)=$A$3)*( Wrestlers_Statistics!$M$3:$M$64998))

I've also tried using the =SUBTOTAL function, as well as Defining a
Name, and oddly enough both do the SAME thing. I'm out of ideas.

I can get all of my functions to work if I tweak the cell back after
the submit Macro changes the cell call, but what do I do to KEEP it
there?

I'd be happy to email anyone a copy of my program... I just really need
help.


Thanks.


--
rmolitor
------------------------------------------------------------------------
rmolitor's Profile: http://www.excelforum.com/member.php...o&userid=29190
View this thread: http://www.excelforum.com/showthread...hreadid=489179

Array Formula Problem
 

Try this:

=SUM(((INDIRECT("Wrestlers_Statistics!$B$"&2):$B$6 4998)=$A$3)*(INDIRECT("Wrestlers_Statistics!$M$"&2 ):$M$64998))


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489179

Array Formula Problem
 

I appreciate the help, and I was starting to play with the INDIRECT
function (new to me), but this particular function doesn't work. It
returns a #VALUE! error. When I Evaluate the Formula, the first item
it displays as in error is SUM(((_Wrestlers_Statistics!$B$2:$B$64998)_

After pressing Evaluate, it returns SUM(((#VALUE!)=$A$3)...

Something is wrong with that first part.

I've copied it exactly as written, and I've tried both CTRL-ALT-ENTER,
and just ENTER.

Neither works. Is there just a syntax error is is there something else
I can try?


--
rmolitor
------------------------------------------------------------------------
rmolitor's Profile: http://www.excelforum.com/member.php...o&userid=29190
View this thread: http://www.excelforum.com/showthread...hreadid=489179

Array Formula Problem
 

Does this work for you ?


=SUM(((INDIRECT("Wrestlers_Statistics!$B$"&2&":$B$ "&64998)=$A$3)*(INDIRECT("Wrestlers_Statistics!$M$ "&2&":$M$"&64998))))


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489179

Array Formula Problem
 

Thank you Viti, this did indeed get my problem fixed. I also used a
very similar formula to seek out a specific string from a column on
another page using the INDIRECT funtion and add them up. That worked
wonderfully!

Thanks for you help!


--
rmolitor
------------------------------------------------------------------------
rmolitor's Profile: http://www.excelforum.com/member.php...o&userid=29190
View this thread: http://www.excelforum.com/showthread...hreadid=489179