Applying range name a posteriori across worksheets

[letallec]

Here is an example to illustrate an issue with excel 2007 and applying name after the fact. I have column of numbers in Sheet1, say A1:A4, to which I can define the name "Num". Now if in Sheet1 I sum(A1:A4), I can apply name Num by using the Apply name feature.

However, if I do sum(Sheet1!A1:A4) then excel says it does not find a match to apply name. The same problem happens if the sum takes place in Sheet2, where in all cases the reference to Num will be Sheet1!A1:A4. Is this a general problem or am I the only one?
Is there a work around such that one can apply name across the workbook?

 

[shg]

Welcome to the board.

In my experience, the Apply Names method doesn't work all that well. With caution, you could find and replace Sheet1!A1:A4 with Num, but the better solution is to define names beforehand and apply them as you go.

I tend to err on the side of defining more names than I need and deleting unused names later.