Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |