none
#Error on ever cell except one in MS Project RRS feed

  • Question

  • Hello I created a custom field named Budget to Date. In this field I have a formula that is written like this [Budget Cost]-[Cost]. My formula works in row 0 but in every other row it displays #ERROR.

    How do I get it to show the same value as row 0? Another solution would be to have nothing shown in all rows >0 too.

    Thank you

    Thursday, February 21, 2013 9:54 PM

Answers

  • trini_man,

    Budget cost is only applicable to the Project Summary Task which is ID 0. Try the following formula:

    IIf([ID]=0,[Budget Cost]-[Cost],0)

    Don't forget to check the custom field option to use the formula for task and group summary rows.

    John

    • Marked as answer by trini_man Thursday, February 28, 2013 7:21 PM
    Thursday, February 21, 2013 10:23 PM
  • trini_man,

    Okay, I'll give you two solutions. If having nothing in the custom field is acceptable then it can still be done with a formula, however the formula will have to be in a custom text field, not in a custom cost field. For this approach use the following:

    Text1=IIf([ID]=0,[Cost]-[Budget Cost],0)

    If you would rather duplicate the value for all tasks, then that will have to be done using VBA. The following code will put the desired value in the Cost1 field:

    Sub FillDwnBudCost()
    Dim t As Task
    Dim PST As Task
    Dim DelCst As Single
    Set PST = ActiveProject.ProjectSummaryTask
    DelCst = PST.BudgetCost - PST.Cost
    PST.Cost1 = DelCst
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Cost1 = DelCst
        End If
    Next t
    End Sub

    John

    • Marked as answer by trini_man Thursday, February 28, 2013 7:21 PM
    Friday, February 22, 2013 2:39 AM
  • trini_man,

    Having the Cost1 field recalculate each time the Cost field changes can be done with an Event based macro but it is more complex to set up, so unless you really need that functionality, I'd stick with the custom button to run the macro.

    With regard to setting the Cost1 field font color to red, I've modified the macro as shown below.

    Sub FillDwnBudCost()
    Dim t As Task
    Dim PST As Task
    Dim DelCst As Single
    Set PST = ActiveProject.ProjectSummaryTask
    DelCst = PST.BudgetCost - PST.Cost
    PST.Cost1 = DelCst
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Cost1 = DelCst
        End If
    Next t
    SelectTaskColumn Column:="cost1"
    Font32Ex Color:=255
    SelectBeginning
    End Sub

    John

    • Marked as answer by trini_man Wednesday, March 13, 2013 3:26 PM
    Wednesday, March 13, 2013 2:18 AM

All replies

  • trini_man,

    Budget cost is only applicable to the Project Summary Task which is ID 0. Try the following formula:

    IIf([ID]=0,[Budget Cost]-[Cost],0)

    Don't forget to check the custom field option to use the formula for task and group summary rows.

    John

    • Marked as answer by trini_man Thursday, February 28, 2013 7:21 PM
    Thursday, February 21, 2013 10:23 PM
  • Hello John, this work yes thank you. However, is there a way to just have the cells blank instead of $0.00? A even better approach would be to have the other cells that display $0.00 to have the same value as cell 0. So in other words all cells will have the same value as cell 0.

    Thank you


    • Edited by trini_man Thursday, February 21, 2013 11:34 PM
    Thursday, February 21, 2013 11:24 PM
  • trini_man,

    Okay, I'll give you two solutions. If having nothing in the custom field is acceptable then it can still be done with a formula, however the formula will have to be in a custom text field, not in a custom cost field. For this approach use the following:

    Text1=IIf([ID]=0,[Cost]-[Budget Cost],0)

    If you would rather duplicate the value for all tasks, then that will have to be done using VBA. The following code will put the desired value in the Cost1 field:

    Sub FillDwnBudCost()
    Dim t As Task
    Dim PST As Task
    Dim DelCst As Single
    Set PST = ActiveProject.ProjectSummaryTask
    DelCst = PST.BudgetCost - PST.Cost
    PST.Cost1 = DelCst
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Cost1 = DelCst
        End If
    Next t
    End Sub

    John

    • Marked as answer by trini_man Thursday, February 28, 2013 7:21 PM
    Friday, February 22, 2013 2:39 AM
  • trini_Man,

    Which field are you using for the "budget to date" data? And how are you calculating the data?

    John

    Tuesday, March 12, 2013 2:33 AM
  • Hello John, I am using the a Cost Field and I am calculating like like: IIf([ID]=0,[Budget Cost]-[Cost],0)

    How do I use the FillDwnBudCost function?

    Tuesday, March 12, 2013 3:40 PM
  • trini_man,

    It's a little confusing because you talk about a "budget cost to date" value. Is that what you mean by the Cost field?

    At any rate, to run the macro simply go to Developer/Code group/View macros. Select the FillDwnBudCost macro from those listed and hit "Run". Note: this assumes you have already copied the above macro code into the VB Editor.

    John

    Tuesday, March 12, 2013 4:04 PM
  • Hello John, you have been a great help for me.

    However, besides having a button and recalculating the maco is there a way to have it recalculate automatically when the cost column is changed?

    Also how do I make  t.Cost1 in the color Red?

    Thank you


    • Edited by trini_man Tuesday, March 12, 2013 10:00 PM
    Tuesday, March 12, 2013 9:35 PM
  • trini_man,

    Having the Cost1 field recalculate each time the Cost field changes can be done with an Event based macro but it is more complex to set up, so unless you really need that functionality, I'd stick with the custom button to run the macro.

    With regard to setting the Cost1 field font color to red, I've modified the macro as shown below.

    Sub FillDwnBudCost()
    Dim t As Task
    Dim PST As Task
    Dim DelCst As Single
    Set PST = ActiveProject.ProjectSummaryTask
    DelCst = PST.BudgetCost - PST.Cost
    PST.Cost1 = DelCst
    For Each t In ActiveProject.Tasks
        If Not t Is Nothing Then
            t.Cost1 = DelCst
        End If
    Next t
    SelectTaskColumn Column:="cost1"
    Font32Ex Color:=255
    SelectBeginning
    End Sub

    John

    • Marked as answer by trini_man Wednesday, March 13, 2013 3:26 PM
    Wednesday, March 13, 2013 2:18 AM
  • Works great!! Thank you
    Wednesday, March 13, 2013 3:26 PM
  • trini_man,

    You're welcome and thanks for the feedback.

    John

    Wednesday, March 13, 2013 3:33 PM