File references

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

im using Excel9

i have one master xls file that has about 20 other files that link to it. the master one has like a half million cells that link to the other files. what i want to do is have a filenames.xls file that lists the names of the files, then pull the names from there, so i dont have to update all half a million cells when i change the name of a fil

for example, instead of

MyCell
=SUMIF('[MyFile.XLS]MyWorksheet'!$D$5:$AB$5,DV$5,'[MyFile.XLS]MyWorksheet'!$D10:$AB10

i want to do something like

A1
'[MyFile.XLS]MyWorksheet

MyCell
=SUMIF(A1!$D$5:$AB$5,DV$5,A1!$D10:$AB10

this doesnt work the way i have posted i
does anyone know if this sort of thing is possible? maybe there is some syntax i need to be using i am unfamiliar with..

much thanks for any hel

you can email nerevar at shaw dot c
 
another way to look at it i

is it possible to convert a string to a reference

so if i can get the string "'[Myfile.xls]MySheet'!A1:Z100" (for example
can i convert that to a real range/reference?
 
Nereva

Assuming that both files are in the same directory

Sheet [Bubblsrt.xls]Sheet1! 'B4
Range b2:d2 'B5
Path [Bubblsrt.xls]Sheet1!b2:d2 'B6

=SUM(INDIRECT(B6))

Indirect works wonders!

Regards
Peter
-----Original Message-----
im using Excel97

i have one master xls file that has about 20 other files
Click to expand...
that link to it. the master one has like a half million
cells that link to the other files. what i want to do is
have a filenames.xls file that lists the names of the
files, then pull the names from there, so i dont have to
update all half a million cells when i change the name of
a file
for example, instead of:

MyCell:
=SUMIF('[MyFile.XLS]MyWorksheet'! $D$5:$AB$5,DV$5,'[MyFile.XLS]MyWorksheet'!$D10:$AB10)

i want to do something like:

A1:
'[MyFile.XLS]MyWorksheet'

MyCell:
=SUMIF(A1!$D$5:$AB$5,DV$5,A1!$D10:$AB10)

this doesnt work the way i have posted it
does anyone know if this sort of thing is possible? maybe
Click to expand...
there is some syntax i need to be using i am unfamiliar
with...
 
another related question i would have i

is it possible to specify that i want the value of a cell, rather than the reference

IE: if i have some function MyFunc that wants a reference you might use it like
MyFunc(A1

but now what if i want to store the text of a reference in A1, and then use the _value_ of A1 instead
i dont know what the syntax is but it would be something like

MyFunc (Value(A1)
or MyFunc(!A1)
 
Back
Top