This query cross-posted at http://www.ozgrid.com/forum/showthread.php?t=173141 and http://www.excelguru.ca/forums/showt...=6035#post6035
For a workbook that records and calculates vehicle fuel consumption I've combined two UDFs into one that serves both
functions, depending upon the first argument passed by the formula. However, I've now run into recalculation problems. Two
of the nine worksheets have formulas which call the function, but only the sheet that's active when the workbook is opened
calculates correctly; the other has to be forced to recalculate with F9. If neither sheet is active when the workbook is
opened then both show incorrect figures.
Having searched for answers I've incorporated Application.Volatile in the function but it makes little difference. The
other suggestion was to pass all ranges needed for calculation as arguments, but how on earth could that be done?
Here's the UDF. Its purpose is to find the previous row when the tank was filled, when there might be a number of blank
cells (if the tank was only partially filled):
I'm attaching a slimmed-down sample of the workbook containing only the two sheets in question. Their layout is identical, with the exception of formulas in col. I. The obvious problem seems to lie with col. J ("Dist."), which calculates distance since the tank was last filled, but other calculations are also faulty. The two coloured rows at the foot of worksheet "Cefiro MPG" show fixed, correct values for the two similarly coloured rows with formulas, as a check for correct calculation.Please Login or Register to view this content.
With grateful thanks for any help that may be forthcoming.
Richard
Bookmarks