Excel Formula too long

[hopelessliar]

I have developed a collection of interlinking workbooks on my local machine. Now that I am happy with the way they work, I want to put them on the network but I have run into a problem, the path lengths are now longer and as aresult, some formulas are too long!

Does anyone know a simple way round this?

Oh yes, excel97 - in case that makes a difference.

 

[KenWright]

Define some names and reference other workbooks, eg whilst in your summary workbook, define a name to point you to a cell in your target workbook:-

Insert / Name / Define

Dat1 ='[ABCDE Summary 14 05 2003.xls]ABCDE Estimate Summary'!A1

Dat2 ='[ABCDE Summary 14 05 2003.xls]ABCDE Estimate Summary'!A2


Now instead of say ='[ABCDE Summary 14 05 2003.xls]ABCDE Estimate Summary'!A1 * '[ABCDE Summary 14 05 2003.xls]ABCDE Estimate Summary'!A1

you can have = Dat1 * Dat2

Regards
Ken..............

 

[hopelessliar]

I considered that, but the thing is, we're talking about thousands of cells here - which would be a lot of names to define.

In case anyone is interested, I have come up with another solution (of sorts). I have inserted some new worksheets at the end of the summary workbook and simply linked them to the other workbooks such that I have a copy within the summary workbook. That way the formulas are considerably shorter - and my workbook is considerably larger and runs considerably slower (

Still, it works for now.