Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 3 down vote accepted

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.

share|improve this answer
Thanks Neal; I'll go for method 1, since I'm on mac (no VBA). How can I get the sheet names automatically? – CharlesB Jun 8 '10 at 8:26
This will give you the sheet name. This only works if the file is not a temporary excel file (has an actual save location): =MID(CELL("filename",E6),1+FIND("]",CELL("filename",E6)),100) – Jarvin Jun 8 '10 at 15:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .