Forum Discussion
Solved
Calculating MODE Across Several Worksheets
Hello. I have data in 11 different cells across several worksheets. For example, I have 20 worksheets in a single workbook and those worksheets all have data in cells D3, D5, D7, D9, D11, D13, D15,...
=MODE(TOCOL(Sheet1:Sheet20!D3))where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.
Regards
=MODE(TOCOL(Sheet1:Sheet20!D3))
where Sheet1 and Sheet20 are the leftmost and rightmost of the 20 worksheets respectively.
Regards
Thanks JosWoolley! Works great and I have one question, based on the fact that the number of worksheets will be dynamic over time. For other statistics I created code that lists the names of all the worksheets in the workbook, less the summary sheet. To run those other stats I name that list as "All_Worksheets" to deal with the dynamic number of worksheets and refer to it in those formulas using INDIRECT. Can the same be done with TOCOL to capture a dynamic number of worksheets? For example, MODE.MULT(TOCOL(INDIRECT("'"&Summary_Worksheets&"'!D33"))) is not working for me.
Personally I'd prefer to avoid such a set-up, as it requires the volatile INDIRECT. Can you not simply ensure that you have two dummy worksheets, named Start and End for example, which are blank and between which will sit the relevant worksheets? You can then use:
=MODE(TOCOL(Start:End!D3))Failing that, you could use:
=MODE(N(INDIRECT("'"&All_Worksheets&"'!"&CELL("address",D3))))- I really like the TOCOL function and will do the dummy worksheets after I double-check that the blank worksheets do not impact my stats. Thanks again!