Autofilling a formula: not working?

[pikapika13]

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

 

[Guest]

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]

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.

 

[Guest]

Hi

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

Andy.