It works a treat within the personal.xls file but when I open a fresh
workbook it doesn't! I get a #NAME error.
...any help would really be appreciated!

Hi. Just to add. Another debugging technique is to go to your worksheet,
and hit the "Insert Function" button ("Fx") located next to the formula bar.
From the Category list, select "User Defined" By inserting your formula
from here, you will notice that your formula is preceded by "Personal.xls!".
If you don't see your function at all in this list, it may indicate another
problem. It usually means you placed the formula on a worksheet module, so
it won't run anyway.

Just to add...Since your formula works, I would assume you do not have the
option "Require Variable Declaration" turned on.
It's "usually" a good technique to turn this on. If interested, go to the
vba editor, and do Tools | Options | Editor tab | and check on "Require
Variable Declaration"

The disadvantage of course is that your formula is a little longer since you
have to use Dim statements...

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)

Dim diffa
Dim diffb
Dim facta
Dim diffc
....etc
diffa = (Lwt2 - lwt1)
diffb = (tgtwt - lwt1)
....etc

You could write it like this, with the last variable in the formula
purposely misspelled. The program will now catch the misspelling.

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
ITP = yield1 + ((tgtwt - lwt1) * (Yield2 - yield1)) / (Lwt2 - lwt1l)
'<--
End Function


Just another option, but this may be a little slower...

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
With WorksheetFunction
ITP = .Forecast(tgtwt, Array(yield1, Yield2), Array(lwt1, Lwt2))
End With
End Function

--
Dana DeLouis
Win XP & Office 2003


wrote in message
oups.com...
Hi there folks.
I am trying to add a formula to my personal.xls file for use in all
workbooks.
Here it is:-

' Interpolate values

Function ITP(tgtwt, lwt1, Lwt2, yield1, Yield2)
diffa = (Lwt2 - lwt1)
diffb = (tgtwt - lwt1)
facta = diffb / diffa
diffc = Yield2 - yield1
factb = diffc * facta
ITP = yield1 + factb
End Function

Nice and simple.
It works a treat within the personal.xls file but when I open a fresh
workbook it doesn't! I get a #NAME error.
If I then unhide personal.xls and try it in the personal file it's
fine.

It's really confusing me and any help would really be appreciated!

It's Excel 2003 running on Windows XP (SP2)

Thanks very much,

Wullie