Declaring a global variable for a string in Excel or VB

[stonypaul]

If I have cause to populate a cell by referencing another cell in another worksheet is it possible to shorten the reference by declaring a global variable with a string value which will replace the common part of the re-occurring reference?
For instance if I have references in two separate cells which are:-

=C:\SUPPORT\Finances\[paid_recd.xls]week1!A1

and

=C:\SUPPORT\Finances\[paid_recd.xls]week1!A2

Is it possible to replace the common part of the string with a variable so you end up with something like:-

=week1_A1

and

=week1_A2

So the variable has a string value of "C:\SUPPORT\Finances\[paid_recd.xls]week1!"

I would prefer to do this in Excel but can cope with VB if necessary, or is there an even better way of doing this altogether?

Thank you

 

[xenou]

You could do this with INDIRECT(), or with a user-defined function written in VBA). But both would be a performance hit. So its best to leave it as it is.

You can shorten the reference (temporarily) by opening the external workbook being referred to - then it will show only the workbook name, not the full path. It will also speed it up if there are heavy links, such as large lookup tables, involved.