I have an Excel workbook has a sheet for each month, named Jan14, Feb14, etc. I also have a summary sheet that displays key MEDIAN array calculations for several columns for each month in the workbook. For example, one of my array is:
With the array above, each time I add a row for a new month, I need to edit each sheet name used in the array – humbug.
Using Excel’s INDIRECT function to evaluate the text in the date column of my summary worksheet to arrive at a cell or range reference in my monthly worksheets. In this case, cell A26 on my summary worksheet holds the month name. The following formula utilizes the INDIRECT function to create the range reference used by the MEDIAN array:
Note that I use the ampersand operator to join the month name with the cell reference (expressed as text). Refer to the figure below. If cell A26 contains the text May14, the MEDIAN array returns the median of the range May14!N2:N20000.
Remember to use CONTROL + SHIFT + ENTER to enter an array formula.