Hi Guys
so i have what i thought was simple problem to solve but it seems limited in excel and trying to not go down the VB route (cause i suck)
so above is my formula so far, what i am trying to acheive is doing and index and a search to match a players name and put the results from what they have in the right row and column. which works.
the problem arrises when i am trying to integrate the SUMPRODUCT across multiple sheets (example two, eventually wants to be 24 (long formula i know), the data either adds together if it finds the user in both the sheets but if it only finds it in one sheet it clears the cell to say it has no data which isn't true.
i've tried for ages to get this working (two days) just seems trivial and would be quite simple to run through an array in something like Java to increment through the 24 sheets and add the values together as it goes along (if there is a value to add)
so i have what i thought was simple problem to solve but it seems limited in excel and trying to not go down the VB route (cause i suck)
Code:
=IFERROR(SUMPRODUCT(INDEX(INDIRECT($AP$5&"!$A$3:$AN$4"),MATCH(Consolidation!$B3,INDIRECT($AP$5&"!$B$3:$B$10"),0),MATCH(Consolidation!C$2,INDIRECT($AP$5&"!$A$2:$AN$2"),0)))+INDEX(INDIRECT($AP$6&"!$A$3:$AN$4"),MATCH(Consolidation!$B3,INDIRECT($AP$6&"!$B$3:$B$10"),0),MATCH(Consolidation!C$2,INDIRECT($AP$6&"!$A$2:$AN$2"),0)),"")so above is my formula so far, what i am trying to acheive is doing and index and a search to match a players name and put the results from what they have in the right row and column. which works.
the problem arrises when i am trying to integrate the SUMPRODUCT across multiple sheets (example two, eventually wants to be 24 (long formula i know), the data either adds together if it finds the user in both the sheets but if it only finds it in one sheet it clears the cell to say it has no data which isn't true.
i've tried for ages to get this working (two days) just seems trivial and would be quite simple to run through an array in something like Java to increment through the 24 sheets and add the values together as it goes along (if there is a value to add)
