Autofilling a formula: not working?

I currently have the following formula:
=INDIRECT($A$1&".xls!A1").
I need to drag and increment the formula but it won't work.
Desired results:

INDIRECT($A$1&".xls!A2")
INDIRECT($A$1&".xls!A3")
etc.

Thanks in advance for your help.

Hi

Try this:
=INDIRECT($A$1&".xls!A"&ROW()-2)
where the row number minus 2 is the number you want. The example above would
look at the row number the formula is on and subtract 2 from it.
This will then increment when you fill down (as the row number increments).

Andy.

"pikapika13" <[email protected]> wrote
in message news:[email protected]...
>
> I currently have the following formula:
> =INDIRECT($A$1&".xls!A1").
> I need to drag and increment the formula but it won't work.
> Desired results:
>
> INDIRECT($A$1&".xls!A2")
> INDIRECT($A$1&".xls!A3")
> etc.
>
> Thanks in advance for your help.
>
>
> --
> pikapika13
> ------------------------------------------------------------------------
> pikapika13's Profile:
> http://www.excelforum.com/member.php...o&userid=10892
> View this thread: http://www.excelforum.com/showthread...hreadid=523128
>

Thanks for the quick reply. It works. Now how do I have it for the columns? the "column" function gives me a number. I would need a letter.

Hi

Thanks for the feedback. If you use COLUMN() it will return the column
number - just the same as ROW() does for rows!

Andy.

"pikapika13" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks for the quick reply. It works. Now how do I have it for the
> columns? the "column" function gives me a number. I would need a
> letter.
>
>
> --
> pikapika13
> ------------------------------------------------------------------------
> pikapika13's Profile:
> http://www.excelforum.com/member.php...o&userid=10892
> View this thread: http://www.excelforum.com/showthread...hreadid=523128
>