Ok, I have a table with 10 rows. Each row is pulled through from the bottom row of another workbook. I have 10 such workbooks, and this table pulls through the bottom row from each to make a sort of "summary" table.
It used INDEX MATCH to get the data, and pulls it through just fine. In all 11 workbooks (10 + summary) the tables are in A4:Z11.
I have a totals row in my summary table, and I noticed that in column R, the total is 0. All other column's total row is correct.
R4:R9 are indeed 0, which is correct, but R10 is pulling through £12,531 and R11 £112,137, again both correct. So surely the total row for R should be £124,668?
Now what's weird is when I select R10 and R11, the little SUM you get at the bottom status bar of Excel says 0. The COUNT is correct, saying 2, but SUM says 0. If I copy and paste values on R10 and R11, that little SUM in status bar still says 0.
However if I manually type 12531 and 112137 into R10 and R11, the SUM at the bottom (and the totals row of the table) add up correctly.
What's even weirder is if I select Q10 and Q11 or P10 and P11 (which all have non zero numbers in), the SUM at the bottom status still says 0 (again, the COUNT is correct) but in these columns the totals row is adding just fine anyway!
The formula for every cell in my summary table is:
=INDEX(path\[Workbook.xlsx]Sheet1!$A$4:$Z$11,MATCH($A5,'path\[Workbook.xlsx]Sheet1!$A$4:$A$11,0),MATCH(R$4,path\[Workbook.xlsx]Sheet1!$A$4:$Z$4,0))(obviously the lookup reference changes in each cell)
Also posted here: http://www.mrexcel.com/forum/e…lls-only.html#post4504307