Return Min Values Across Several Sheets

I came up with two solves for this.

The first uses a 3-D CountIf. Somewhere make a list of the sheet names and then name that range. I called mine Sheets, which refers to the words Schenker and MTA.

=SMALL(INDIRECT("'" &Sheets&"'!" & SUBSTITUTE(ADDRESS(1,COLUMN(F3),4),1,"") & ROW(A3)),
COUNTIF(INDIRECT("'" &Sheets&"'!" & SUBSTITUTE(ADDRESS(1,COLUMN(F3),4),1,"") & ROW(A3)),0)+1)


...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Then just copy over and down as needed.

Or, you could just VBA which would be considerably less burdensome on your processor. (The former solution uses lots of INDIRECT!)

Please Login or Register  to view this content.

The code accounts for the difference in your data formatting, which is why the formula references begin on row 3, and the procedure begins on r + 1.

Here is the revised code, which outputs the sheet names.

Please Login or Register  to view this content.

Nothing would really need to be changed. The code is skipping any sheet named Analysis when it does its comparison. If you want to skip other sheets you could list them as well, otherwise all other sheets will be included.

The only other thing to keep in mind are the ranges and how it matches up. This code runs from on Analysis, from Cell coordinates 2,2 to Cell Coordinates 17,30. On the other sheets, this cell reference lines up with r +1, c + 4 since the other sheets are slightly misaligned to Analysis. For the first cell 2,2 this matches up to (2+1,3+4) = (3, 7) = F3.

Please Login or Register  to view this content.