Formula jumps back to #NA until I hit return

[Nfraser71]

Hi

I have a formula in Excel 2007 that uses index/ match. The Match value refers to the sheet name the formula exists on.
The formula works fine but when I click another cell in another sheet the formula result that originally was ok now shows #NA. I then have to manually hit return to get them all to calculate again.
Any ideas how I can avoid having to continually hit return?

=(E2/(SUMPRODUCT(($A$2:$A$59=A2)*$E$2:$E$59)))*((INDEX(INDEXAREA;MATCH(REPLACE(CELL("filename");1;FIND("]";CELL("filename"));"");MATCHROWS;0);MATCH($A2;MATCHCOLUMNS;0))))

Thanks.
Neil

[:) Sixthsense :)]

Because Cell() is a Volatile function which calculates on any change in the excel application.

You should not ignore the last argument (Reference) of the Cell() function if you want it to stick with the current file where it resides.

So your formula should be converted to

=(E2/(SUMPRODUCT(($A$2:$A$59=A2)*$E$2:$E$59)))*((INDEX(INDEXAREA;MATCH(REPLACE(CELL("filename",$A$1);1;FIND("]";CELL("filename",$A$1));"");MATCHROWS;0);MATCH($A2;MATCHCOLUMNS;0))))

[Nfraser71]

Thank you Sixthsense!
Worked perfectly

[Richard Buttrey]

Thank you Sixthsense!
Worked perfectly

Rep added to 6thSense for an excellent and succinct explanation.

You've received the answer and hopefully you've understood the explanation which is just as important.