external functions

[dido22]

Hi,

I have a complex (and rather messy) formula in my spreadsheet which is used
in all rows. It works fine, no problem.

Is there any way of defining the formula once, elsewhere, perhaps in a
separate sheet, then 'calling' it in the main worksheet as if it were a
subroutine or sub-functtion.

I am thinking in terms of Basic programming, where there are subroutines &
subfunctions, but I dont know anything about VBA & prefer to avoid it.

Thanks for all ideas

KK

 

[James Ravenswood]

Post your formula. We need more information.

 

[Stan Brown]

I have a complex (and rather messy) formula in my spreadsheet which is used
in all rows. It works fine, no problem.

Is there any way of defining the formula once, elsewhere, perhaps in a
separate sheet, then 'calling' it in the main worksheet as if it were a
subroutine or sub-functtion.

I am thinking in terms of Basic programming, where there are subroutines &
subfunctions, but I dont know anything about VBA & prefer to avoid it.

Yes. Alt-F11, then Insert » Module. In the module, create a public
function. You can then use that function in any sheet of this
workbook.

If you have Excel 2007 or 2010, you will have to save the workbook as
an .xlsm, not an .xlsx. When you open it, you'll get a warning about
macros -- "active content", I think. You can suppress the warning
permanently in several ways:

Do you have to live with this message for all your workbooks? No,
you can keep your workbook in a trusted location that you have listed
in Excel. Look in Excel 2010 help for "Add, remove, or modify a
trusted location for your files" or Excel 2007 help for "Create,
remove or change a trusted location for your files".