Excel Guru Needed... Array Functions and Looking Up References from Another Workbook

[AtlantaBob]

I'm doing some work for a law firm that is trying to automate routine pleadings. Note that I'm limited to using Excel and Word for these projects.

There are many different types of pleadings, but all rely on a single data set of court addresses, costs, etc. This is all currently stored in a file CourtAddresses.xls in a main directory, and I would like to have the other pleadings stored in a directory underneath this one.

~/CourtAddresses.xls
~/Pleading1/Pleading1.xls
~/Pleading2/Pleading2.xls
...

The problem comes when I'm writing array lookup formulas in the Pleading files (an example follows). The logic is quite simple, but reading (and editing) the formulas is crazy difficult because so much of the formula is devoted to the directory path. This information is also frequently moved between computers, necessitating changes to the formulas each time to replace the directory reference.

=INDEX('Macintosh HD:Users:michael:Documents:100630 from Laptop:
OfficeAutomation:[CourtAddress.xls]
Address'!C:C,MATCH(Data!$C2&Data!$D2,'Macintosh HD:Users:michael:Documents:
100630 from Laptop:OfficeAutomation:CourtAddress.xls'!
rngType&'Macintosh HD:Users:michael:Documents:100630 from 
Laptop:OfficeAutomation:CourtAddress.xls'!rngCounty,0))

I'm happy using this process (rather than some sort of database function) because the data set is really quite small (say 130 Court records total), but I would LOVE a way to replace these unwieldy file references. I've played around with adding in a cell that lists the directory reference to the CourtAddress.xls file and using CONCATENATE() to use it as necessary, but it really doesn't result in easily readable functions....

Any suggestions are welcome.

Thanks,

Edited to put in linebreaks in the code to make it a little more readable.

 

[ASTOCADDIDS]

Can it be done.. And how would i do it I have a flash drive im going use can anybody help thanks Is it as easy as when i installed windows 7..

 

[AtlantaBob]

Well, that was an interesting reply.

For the moment, I've stumbled on the solution of referencing the external file on another worksheet in the same workbook (seems like it works, since we're only talking about 150 records x 10 fields), naming the relevant ranges, and then referring to those ranges. It definitely cuts down on the length of the formulas.

Any other suggestions? Thanks in advance.