Autofill a colum using relative worksheet but Absolute cell reference

[judyweinstein]

Hi There
I am trying to autofill down a column so that my formula will keep the same cell but use different worksheets (in order). I cant seem to figure this out. For example my worksheet are named
Alan, Kelly, Flor, Millie, Judy, Ian, Eli
I want the first cell to =Alan!$E$3
i want to then autofill so that the next cell will read =Kelly?$E$3, etc....
Any idea how to autofill this? I will be linking for hours otherwise. I tried and indirect formula but couldn't figure that out. THANKS IN ADVANCE

 

[Adjunctor]

INDIRECT should work just fine. Let's say you have your worksheet names on the top row, starting on A1, your first formula should be

=INDIRECT(A1&"!$E$3")

and then just drag to the right.

Likewise if your names are on the first column, starting on A1 then your first formula remains the same but now you drag down: