I am working on a excel staff structure that has an issue with
changing formulas. All formula cells are protected and half are
hidden
but every so often a reference will disapear from a formula. It is
never the same column or row and is only used by only 1 person per
shop no other interferance can be traced. Sometimes it can occour on
a
page that has not been modified.
here is a copy of the formula
=Hours_Worked($A81,C81,C82,$A$2)
hours_worked is the vba part of the program to work out the number of
hours worked and the remainder are reference cells
=Shift_Pay($A81,C$4,C81,C82,$G$2,$D$3,$A$2) +$K84/7
shift_pay works out the pay rates and any penalities in vba and gives
a $ figure

=Hours_Worked($A81,#REF!,#REF!,$A$2)
this is what I get which gives the errors
=Shift_Pay($A81,D$4,#REF!,#REF!,$G$2,$D$3,$A$2) +$K84/7


it is very random when and where it happens, It's like there is a
ghost in it doing what it wants
can any one help me?

Hi

Take a look at your VBA code.
It looks like the cells are being Deleted when some event happens,
rather than being cleared within the code.


--
Regards

Roger Govier


"PJG" wrote in message
oups.com...
I am working on a excel staff structure that has an issue with
changing formulas. All formula cells are protected and half are
hidden
but every so often a reference will disapear from a formula. It is
never the same column or row and is only used by only 1 person per
shop no other interferance can be traced. Sometimes it can occour on
a
page that has not been modified.
here is a copy of the formula
=Hours_Worked($A81,C81,C82,$A$2)
hours_worked is the vba part of the program to work out the number of
hours worked and the remainder are reference cells
=Shift_Pay($A81,C$4,C81,C82,$G$2,$D$3,$A$2) +$K84/7
shift_pay works out the pay rates and any penalities in vba and gives
a $ figure

=Hours_Worked($A81,#REF!,#REF!,$A$2)
this is what I get which gives the errors
=Shift_Pay($A81,D$4,#REF!,#REF!,$G$2,$D$3,$A$2) +$K84/7


it is very random when and where it happens, It's like there is a
ghost in it doing what it wants
can any one help me?

Another thought:

You may have protected the formulas in the cells but what about the cells themselves?

i.e. if another deletes cells, or rows or columns th #REF can occur. Realize that #REF is telling
you that the information in the cell to which it was linked is now gone!

EagleOne

Hi Peter,
If you were to delete Column C between uses
then you would get that error.

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"PJG" wrote in message oups.com...
I am working on a excel staff structure that has an issue with
changing formulas. All formula cells are protected and half are
hidden
but every so often a reference will disapear from a formula. It is
never the same column or row and is only used by only 1 person per
shop no other interferance can be traced. Sometimes it can occour on
a
page that has not been modified.
here is a copy of the formula
=Hours_Worked($A81,C81,C82,$A$2)
hours_worked is the vba part of the program to work out the number of
hours worked and the remainder are reference cells
=Shift_Pay($A81,C$4,C81,C82,$G$2,$D$3,$A$2) +$K84/7
shift_pay works out the pay rates and any penalities in vba and gives
a $ figure

=Hours_Worked($A81,#REF!,#REF!,$A$2)
this is what I get which gives the errors
=Shift_Pay($A81,D$4,#REF!,#REF!,$G$2,$D$3,$A$2) +$K84/7


it is very random when and where it happens, It's like there is a
ghost in it doing what it wants
can any one help me?