Custom user function without using VBA

Yes, it's possible if you use Excel Named Formulas.

For instance, suppose that you need to calculate the difference between the sums of two consecutive columns (A5:Ax - B5:Bx) in different places in your workbook (x is the final row of the each column):

So you define in A11 a name called diff (any name can be used) as =Sum(A$5:A10)-Sum(B$5:B10), assuming that data start in row 5 until the previous row. It could be any cell, not just A11, but the definition changes in the same way.

Unfortunately, Excel 2010 inserts absolute prefixes ($) and worksheet prefixes, so you need to erase the prefixes, but keeping the exclamation marks and erase most $ characters.

When you move the formulas, most references are relative. So it always calculates the difference between the current column and the following, starting from the row 5 until the line before the total line.

So if you have data between C5 and D100 you put just =Diff in C101 and it calculates Sum(C5:C100) - Sum(D5:D100).

Obviously, you can use local or global names in the named formulas, like you mentioned in your question.

You can read more details in Named Formulas.

I know you said no VBA, but doing as follows does NOT require you actually re-write your formulas, know much about VBA, nor maintain your formulas in VBA. You can write it once and forget about it.

Create a User-Defined Function to extract the formula from a cell as a string:

Function GetFormula(Target As Range) As String
    GetFormula = Target.Formula
End Function

Create another to evaluate a string like a formula:

Function Eval(Ref As String)
    Application.Volatile
    Eval = Evaluate(Ref)
End Function

If you had your master formula in sheet1!a1, then you'd put this in every cell that needs to use it:

=eval(getformula(sheet1!a1))