Excel - create a list from each cell of a sheet range

Question

I have a bunch of sheets in my workbook, in each one there is an email address at B6.

What I want to do is to make a list with all the email addresses. I'm familiar with sheet range (all my sheets are between a sheet "first" and a sheet "last"), so I can do something like first:last!B6 to refer to the range. But what is the formula?

Answer 1

Method 1: Create a list of sheet names and use the INDIRECT function. Here is an example.

Method 2: Use a User Defined Function to reference the sheets by index. Here is a function that should suit you.