HELP - UDF recalculation upon opening workbook

[TY]

Hi,
I have a workbook that has UDF cells, and these UDF make references to
other cells in the worksheet:
Function foo() is to retrieve values from database.

A B C
1 V1 V4 =foo(A1,B1)
2 V2 =foo(A2,B1)
3 V3 =foo(A3,B1)

C1 was created by typing '=foo(A1,B1)' directly, whereas C2 and C3 are
created by copying C1, and making changes to the 2nd argument.

The workbook is saved.
When opened in automatic calculation mode, all 3 UDFs are refreshed
properly.

Now, modify the source UDF (cell C1) without referencing A1:
A B C
1 V1 V4 =foo("V1",B1)
2 V2 =foo(A2,B1)
3 V3 =foo(A3,B1)

and save. When opened in automatic calculation mode, cells C2 and C3 are
refreshed, but C1 is not.

Anyone knows why?

And when I copy all the cells and paste them into a new workbook, cell C1
will become #NAME.

Any idea will be helpful.
Thanks.

Teresa

 

[Bernard Liengme]

Hello Teresa,
A couple of observations:
1) If you enter C1 as =foo(A1,B$1) then when you copy it (by dragging the
fill handle or by double clicking the fill handle since you have made a
table) you will get the formulas required without the need to edit. Look in
Help by typing Absolute and selecting the item Switch between relative,
absolute, and mixed references.
2) When you copy the formulas to a new workbook you get the NAME error
because the new book does not have the foo UDF. If foo lives in a workbook
called Book1 then you must use the formula =Book1!foo(A1,B$1). For this to
work successfully Book1 must be open. Tat problem is overcome by putting the
UDF in a PERSONAL.XSL file that always opens.
3) What makes you think "When opened in automatic calculation mode, cells C2
and C3 are refreshed, but C1 is not"?
Best wishes

 

[TY]

Hi Bernard,

Thanks for your response. Here are the answers to your observations:

2. The workbook that has the UDF defined was opened, and #NAME should not
happen.

#NAME only appears in C1, but not in C2 and C3.

3. Before opening up the workbook again, I have modified the values in the
database, both C2 and C3 are refreshed with the new values, but C1 was not.