Excel formulae calculation

I have a spreadsheet that lists 400 items.  

Each item has a set of eight dates which reflect the project lifecycle of the item from requirements signed off, specification signed off through to test sign off and Live date.

I have a function called Status that takes as input these eight dates and another pivot date and returns the current status of the item as per the pivot date.
Now my question is as follows.  Each function in the status column is

Each time one of the dates is changed then Excel recalculates the entire spreadsheet rather than just the one line.  

Is there any way of getting round this?  

Calling Date in each function is inefficient so
What I have tried is to store the date in a cell in the spreadsheet and then had the status function thus:
and then in the Status function retrieved the Date value from the cell.  However this approach was also slow and had other problems associated with it.

Is there any way of storing variables with the spreadsheet?

Thank you,

Who is Participating?
criConnect With a Mentor Commented:
How about this: Instead placing the 400 formulas in the sheet, set up a sheetchange event reacting to only to changes in the cells which _would_ act on the a.m. functions. Calculate the status in a separate sub and write the result back to the appropriate cell. I use a similar set up to set a calc ID number. If you need more, please ask.  
Dear GordonTm

Regarding your last question to store variables within the spreadsheet. You can do this by using the Name funtion. Either through the menu or VBA.
Through the menus goto Insert, Name,  Define... Now add a name and under RefersTo you can add the variable you would like to store. When you save and reopen the worksheet the variable will be stored in the name.

In VBA this can be achieve by:
ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=" & Format(Now(), "d-mmm-yy")


gordontmAuthor Commented:
Thank you for your answer.

In my question I said "then in the Status function retrieved the Date value from the cell" - what you have described is exactly what I did.

However as I said that produced other problems.  

I should have said "Are there any *other* ways of storing variables with the spreadsheet" so I apologise for not being clear enough.

I was also actively seeking an answer to the recalculation problem.  The reason I mentioned variables is I thought it might be part of the answer.
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Each time one of the dates is changed then Excel recalculates the entire spreadsheet rather than just the one line.  
 Is there any way of getting round this?  

You can set the workbook to calculate manually instead of automatic. You can do that through code or from Tools, option.

Then you can calculate whatever cell or ranges you need to with the calculate command.  ie.......  Range("a1").Calculate

more later,

In your Status() function, you don't have the statement Application.Volatile in it, do you? This would invoke a recalculation of the whole sheet (see http://support.microsoft.com/support/kb/articles/Q81/8/56.ASP for details). Otherwise, it should only recalculate only for those cells where the precedent cells have changed.

Similarly, have you got Application.Calculate or Activesheet.Calculate in your function?

As to your Date thing, addressing the cell from within your function would be a little slower as it would need to go a fetch the value rather than having it there as an argument when you called the function. I can't see why this would cause problems though. There's no quicker way for this that I can think of.

If you fancy it, here's an article on how to optimise the calculations in your worksheet: http://support.microsoft.com/support/kb/articles/Q72/6/22.ASP
Though, looking through it, I don't think it'll help much.

Similarly, here's an article on how Excel approaches the Automatic/Manual calculation thing. It was even a bit of an eye-opener for me: http://support.microsoft.com/support/kb/articles/q161/5/46.asp

Well, what i'm suggesting you to do is write this line in the begining of the calculation sequence...

and then change its value back to...
once you finished all the needed changes on your worksheet.

hoped i have helped a little...
bye bye.
Didn't I already say that??

gordontmAuthor Commented:
The problem is as Noggy has understood it.  

The problem is not with Manual/Automatic recalculation being set.  Automatic recalculation is defined as recalculating only those formulae whose input cells have changed and not recalculating all the formulae on the sheet (as happens with me).

I want only one formulae to recalculate each time an input cell is changed.

To answer Noggy - I have not got Application.Volatile or Activesheet.calculate etc. in my function.  I will investigate the microsoft support sheets.
gordontm - Could you post the code that you are using in your Status() function so that we could see if there may be something else in there which is making Excel think that it is volatile?
gordontmAuthor Commented:
Here is the code:

'This Function calculates the status of a particular deliverable
'given a Range of eight dates of the Deliverable Lifecycle
'and today's date (or other point of reference)

Function Status(ConsiderDate, RG As Range)
  Dim CellValue As Variant
  Dim LatestDate As Date
  Dim T(8) As String
  Dim PostponedFlag As String

  'Check Postponed
  PostponedFlag = RG(1, 1)
  If PostponedFlag <> "" Then
    If UCase(PostponedFlag) = "P" Then Status = "Postponed": Exit Function
    Status = "Error in Postponed Column": Exit Function
  End If

  LatestStageDone = 0
  LatestDate = 0
  '0 = Nothing Done = Awaiting Approval
  '1 = One thing done = Requirements Signed Off
  '8 = Everything Done = Live

  For j = 1 To 8
    CellValue = RG(1, j + 1)
    If IsDate(CellValue) Then
      BoldFlag = RG(1, j + 1).Font.Bold
      'If Dates are in wrong order
      If LatestDate <> 0 And LatestDate > CellValue Then
        'Code to deal with dates
        'Not implemented yet
        LatestDate = CellValue
      End If
      If BoldFlag Then 'Date is marked expected
        If ConsiderDate < CellValue Then 'Date is in future
          'Do Nothing
        Else 'Date is in past
          Status = "Error: Slippage": Exit Function
        End If
      Else 'Date is marked done
        If ConsiderDate < CellValue Then 'Date is in future
          Status = "Error: Dates marked done must be in past"
          Exit Function
        Else 'Date is in past
          LatestStageDone = j
        End If
      End If
    ElseIf UCase(CellValue) = "N" Then
      'Do Nothing
    ElseIf UCase(CellValue) = "N/A" Then
      'Do Nothing
    ElseIf UCase(CellValue) = "Y" Then
      LatestStageDone = j
    ElseIf LTrim(CellValue) <> "" Then
      Status = "Error:  Invalid Date"
      Exit Function
    End If
  Next j

  T(0) = "Awaiting Approval"
  T(1) = "Requirements Signed Off"
  T(2) = "Specification Complete"
  T(3) = "Specification Signed Off"
  T(4) = "Development Signed Off"
  T(5) = "System Test Signed Off"
  T(6) = "UAT Started"
  T(7) = "UAT Signed Off"
  T(8) = "Live"
  Status = T(LatestStageDone)

End Function
gordontmAuthor Commented:
Thank you - that is a very good idea.  However if I could solve my own problem simply I would prefer that.

From your code, I can't see why Excel would think that it is a Volatile calculation. However, I have not tested it yet. I'll let you know later.
gordontm, thanks for accepting my rather offhand comment as answer, this new feature seems to be really working now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.