How to set the formula?

[Eric]

Does anyone have any suggestions on how to set the formula?
In cell A1, there is a given number 5.
In cell B1, ='5'!A12.
I would like to change the formula by retrieving the 5 from cell A1 instead
of type into the cell.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

 

[Bob Phillips]

=INDIRECT("'"&A1&"'!A12")

 

[Eric]

Thank you very much for suggestions
Could anyone please tell me how to make A within A12 as a variable? so
when I copy this formula into next column, then it will become
='5'!B12 in cell C1
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric

 

[Bob Phillips]

=INDIRECT("'"&$A$1&"'!"&ADDRESS(12,COLUMN(A1)))

 

[Eric]

There is value 3988 in cell A1.
='E:\directory\3988\[file.xls]Sheet 1'!N$14, which is working.

=INDIRECT("'E:\directory\"&$A$1&"\[file.xls]Sheet
1'!"&ADDRESS(14,COLUMN(N$1))), which is not working.
Could you please tell me how to fix the coding?
Thank you very much for any suggestions
Eric

 

[Dave Peterson]

If you use =indirect(), then the sending file has to be open (and you wouldn't
need the path, either!).

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

There is value 3988 in cell A1.
='E:\directory\3988\[file.xls]Sheet 1'!N$14, which is working.

=INDIRECT("'E:\directory\"&$A$1&"\[file.xls]Sheet
1'!"&ADDRESS(14,COLUMN(N$1))), which is not working.
Could you please tell me how to fix the coding?
Thank you very much for any suggestions
Eric

=INDIRECT("'"&$A$1&"'!"&ADDRESS(12,COLUMN(A1)))

--
__________________________________
HTH

Bob