# Array Formula Problem

R

#### rmolitor

Hey all, long time reader, first time poster.

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

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

Problem happens in the array when the add row occurs... my formula als
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 arra
looks like before hitting submit. Second line is what happens afte
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
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 afte
the submit Macro changes the cell call, but what do I do to KEEP i
there?

I'd be happy to email anyone a copy of my program... I just really nee
help.
mailto:[email protected]

Thanks

V

#### Vito

Try this:

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

R

#### rmolitor

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?

V

#### Vito

Does this work for you ?

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

R

#### rmolitor

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!