Indirect function using a cell for a variable file/sheet name

[hrhittn23]

I have been on this for about an hour... My function is:
=INDIRECT ("'["&$L2&".xls]"&$L2&"!E$1")

The formula works in my initial cell. When I go to auto fill in the next column, I want the !E$1 part of the function move to F1, then G and so on. The problem is it's acting like an absolute value.

I'm somewhat new to Excel, so I'm sure this is an easy fix.

[martindwilson]

=INDIRECT("'["&$L2&".xls]"&$L2&"!"&E$1) mind you without setting up this its not tested

[hrhittn23]

That gives me #REF! in the cell I placed it in

[martindwilson]

i cant get your original to work either!
ok edit i can
=INDIRECT("'["&$L2&".xls]"&$L2&"'!E$1")
try
=INDIRECT("'["&$L2&".xls]"&$L2&"'!"&ADDRESS(1,COLUMN(E1),2))

[hrhittn23]

Well, the original works here. Perhaps you don't have the workbooks saved as they should be with the correct cells filled. The function actually works great, its auto-filling that is the problem. When I auto-fill a row below, the L2's move to L3's.. I guess that's why I'm so confused!

Like I said, I am fairly new to excel and have been learning formulas and things like absolutes on the fly.. I figured surely that's where I was tripped up

[hrhittn23]

Perhaps I'm using the wrong formula? What I'm ultimately after is pulling values in from another workbook, and using the auto-fill feature. There will be hundreds of values so manually filling in will take a TON of time! I know both sheets need to be open, that isn't a problem.

Is there a better way to go about this problem?

[martindwilson]

see my last post that works ok, i just set it up and tried it
if you want to fill down as well
=INDIRECT("'["&$L$2&".xls]"&$L$2&"'!"&ADDRESS(ROW(A1),COLUMN(E1),2))
ps your example was missing a '
=INDIRECT("'["&$L2&".xls]"&$L2&"'!E$1")

[hrhittn23]

You are amazing! Thank you VERY much!!!!