Trim down on cell references

[robot]

Hi,

I have a formula that is quite long, and is made a lot longer because it
refers to cells in another workbook, so that the names of the workbook and
worksheet are repeated for each cell reference to the workbook. Is there any
way to tackle this issue?

 

[JE McGimpsey]

With the other workbook named, say, Book1.xls, open, in your target
workbook choose Insert/Name/Define and enter a name for the range in
Book1. For instance

Names in workbook: rng
Refers to: '[Book1.xls]Sheet1'!$A$1:$A$10

Then use the name instead of the direct reference:

=SUM(rng)