Show filename only rather than full path in cell

[Shatin]

I have three workbooks: A, B and C. They are kept in the same folder.
Workbooks A and B are data files, and workbook C is where I do all my
calculations.

The problem I am having is this. Let's say I have a cell in C which is the
difference of the corresponding cells in A and B. On the formula line, the
full paths of the cells are shown, like this

='C:\Documents and Settings\Administrator\My Documents\Project A\First
Stage\[A.xls]Sheet 1'!M23-'C:\Documents and Settings\Administrator\My
Documents\Project A\First Stage\[B.xls]Sheet 1'!M23

This is driving me crazy, because it's really difficult to read the formulas
which can at times be rather complicated. Editing the formulas are virtually
impossible. Since all the files are in the same folder, is there any way I
can have Excel display the above formula simply like this:

==[A.xls]Sheet 1'!M23-[B.xls]Sheet 1'!M23

(When I first input the formulas, the shorter form is shown. However once
the file is saved and re-opened, the longer form is displayed.)

 

[Frank Kabel]

Hi
Open the other workbook and you'll see something like
=[name.xls]sheet1!A1

 

[Bob Phillips]

Shatin,

When the workbooks are open, you will only see the filenames, when they are
closed, you see the path as well. Excel needs that info to know where they
are.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Shatin]

Thank you for the replies, Bob and Frank!