1

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?

1 Answer 1

3

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.

2
  • 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, 2010 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, 2010 at 15:13

You must log in to answer this question.

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