Forum Discussion
How to Display Blank field using custom field
Hi
Have 2 questions with regard to Ms Project custom field usage.
1. I have a very simple question but I don't seem to be able to get it right.
I am trying display a blank field on a new custom column of type number using custom field. Below is the formula I am using:
IIf(([Budget Cost]<>""),Round(([Budget Cost]/186000)*100),"")
I am checking the Budget Cost col and if is not empty then do the maths shown about basically finding some percentage value and rounding the final number.
If Budget Cost is empty/blank then I just want to leave it blank as well do nothing with it. So for the false part I simply said "" it gave an error showing #ERROR I guess why this so - because the new custom field is of a type number and I am trying to store an empty string.
So I even replace the false part with [Budget Cost] meaning if it is empty just copy the empty field into my new custom col. Still it gave me #ERROR
I tried replacing with NULL also did not work. For now I have replace the false part with number 0 and it work fine but I have 0 displayed for those Budget Cost cells with blank field. This is very ugly and I dont like this.
I just want to display an empty field/ blank...How do I that,
2. Also one side question how do a get sum of a row of numbers...at the moment as u can see in my formula I am manually inserting the sum of the Budget Cost column as $186,000 to get my percentages. Is there a function, built in field etc that I can use?
Any help my appreciated.
Rgds
KR
- Okay I've got something that appears to work.
1. In the Resource Usage or Resource Sheet customize the Resource Text1 field with this formula and check the option to "use formula" for task and group summary rows::
IIf([Budget Cost]<>"",round([Number1],1) & "%","")
2. This macro will populate the Resource Number1 field with the desired percentage data
Sub CustomBud()
Dim Den As Single
Dim CFF As String
Den = Round(ActiveProject.ProjectSummaryTask.BudgetCost)
CFF = "iif([budget cost]<>"""",[budget cost]/" & Den & "*100,0)"
CustomFieldSetFormula FieldID:=pjCustomResourceNumber1, Formula:=CFF
CustomFieldPropertiesEx FieldID:=pjCustomResourceNumber1, Attribute:=pjFieldAttributeFormula, _
summarycalc:=pjCalcFormula, GraphicalIndicators:=False, AutomaticallyRolldownToAssn:=False
ViewApply Name:="resource sheet"
ViewApply Name:="Resource Usage"
End Sub
This was challenging.
John
Well, you're right in that the error occurs because you are trying to write a string ("") into a custom Number field. However, even if you change the formula to write a zero, the end result will ALWAYS be zero because the Budget Cost field is only applicable to the Project Summary task so no task field will ever have a budget cost. You cannot show a "blank" in a Number field, but you can use a Text field to show nothing.
On your second question, you can get the sum of a row of numbers by creating a custom field that is the sum of each element in the row (e.g, Number1 + Number2 + etc.). However, I think what you perhaps are asking is how to get the sum of a column of numbers (e.g. sum up the Number1 field). For the latter, you simply check the option "Calculation for task and group summary rows" and select "sum"- Hi John
Thank you for your prompt reply. Appreciate your response.
1. I agree Budget Cost can only be applied to the Project summary. But what I have done is that I created a Table based on Resource Usage and in that table I have a Budget Cost col which displays all the Budget cost for each resource item. However some cells in this column can be blank. Then I created a custom field to calculate the percentages based on my earlier formula I showed. That's where my question is about for those blank cells I want to ignore calculating the percentage.
2. Yes I am asking for the sum of numbers in a column. Referring again to my Budget Cost col in my Resource Usage table I want to sum that column. I am aware that you can sum the summary by selecting an option if there is a group filter set where u will get a summary. In my resource usage table the display has no summary just pure cost items in every columns and the first col are the different resources names as defined in my Resource Sheet.
Hope my reply gives more clarity on my questions.
Look forward to your reply.
Rgds
KR- Okay so are you saying you created a custom field in the Resource Usage view that you are calling "budget cost" but it is not Project's Budget Cost field? If so, then that would have been good to know at the onset.
What is this "table" you created? Is it a lookup table? What is the field name (i.e. Project name not your custom name)?
A screen shot would be really helpful. Remember, I'm not inside your head so I can't see what you are doing or what you are thinking, you have to explain in in terms as you would to a third party. Maybe you can sense my frustration.
When you customize a field in a Usage view (Task or Resource), that formula applies ONLY to the resource or task data, NOT to the assignment data. You cannot create a custom field formula for assignment rows. However, you can use VBA to create a macro that will calculate assignment row data and can also roll it up to the resource or task rows.
