how to refer to the files with regular names? (with formula)

  • Thread starter Thread starter iop
  • Start date Start date
I

iop

e.g. there r some files with regular names: d:\ABC\1.xls, 2.xls, 3.xls, ....
N.xls
i want to refer to some cells of these files in a excel file ( d:\temp.xls),
how to use the formula to make it?

thanks!
 
You can create names referencing external ranges e.g.

Names.Add Name:="myName", RefersTo:="=D:\ABC\[1.xls]Sheet1'!$A$1"

Then refer to these in formula e.g.

=A1 * myName
 
can we make it directly (without using any macro, only formula)?
thanks!
--
cj


Nigel said:
You can create names referencing external ranges e.g.

Names.Add Name:="myName", RefersTo:="=D:\ABC\[1.xls]Sheet1'!$A$1"

Then refer to these in formula e.g.

=A1 * myName

--

Regards,
Nigel
(e-mail address removed)



iop said:
e.g. there r some files with regular names: d:\ABC\1.xls, 2.xls, 3.xls,
....
N.xls
i want to refer to some cells of these files in a excel file (
d:\temp.xls),
how to use the formula to make it?

thanks!
Click to expand...
Click to expand...
 
If you are referring to creating the names then yes, use the Name manager
(xl2007) or in xl2003 use Insert > Name > Define

--

Regards,
Nigel
(e-mail address removed)



iop said:
can we make it directly (without using any macro, only formula)?
thanks!
--
cj


Nigel said:
You can create names referencing external ranges e.g.

Names.Add Name:="myName", RefersTo:="=D:\ABC\[1.xls]Sheet1'!$A$1"

Then refer to these in formula e.g.

=A1 * myName

--

Regards,
Nigel
(e-mail address removed)



iop said:
e.g. there r some files with regular names: d:\ABC\1.xls, 2.xls,
3.xls,
....
N.xls
i want to refer to some cells of these files in a excel file (
d:\temp.xls),
how to use the formula to make it?

thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top