Implementing excel formula in project

[nawab35]

Hi All,

I have implemented a logic to fetch the last value in a set of data(from a set of columns) by a formula like =LOOKUP(900000000000,'Sheet 1'!N1:Z1).
So with this formula I am able to fetch the last value populated in Sheet 1 from the columns N until Z.

If columns X, Y and Z are not filled then this formula fetches the value from column W. And so on.

The issue is that I want to do the same thing in MS Project. So all you excel experts, please help me. Can custom fields help?

Regards.

 

[JulieInMaine]

Hi,

No, sorry. Project's functions are closer to Access than Excel. The custom fields (and formulas) in Project can reference data in other fields on the same record (task or resource) but short of programming, you cannot reference data from another task or resource.

Perhaps if you described a bit more fully what you are trying to accomplish in Project, we can be of more assistance.

Julie

 

[nawab35]

Hi Julie,

Thank you for your note. Let me try to explain the scenario to you.

There are X number of activities added to a project, with start date, end date.
Then they have additional data on the project, like "daily data latest".
This column's data is determined by data in N additional columns. The data in "daily data latest" is the last cell in the N additional columns I talked about in the previous point. A sample project file is attached. If you could help me with that it would be great.

Regards.
Regards.

 

[JulieInMaine]

I've opened the project file and still really don't know what you're trying to accomplish. Project does not have a VLookup function.
You could use either a switch function or an IIf function to test whether a field is empty and then display the results of a field in another field -- but eventually you will run out of custom fields in Project. You've used the Number1 through Number4 fields -- there are only 20 Number fields.

The next question is what do you want to do with those values? You cannot over right any fields used by Project for calculation -- you could only either copy/paste into a standard field or use another custom field.

Again -- short of programming (VBA) to loop through each task -- see if a field had a value and then right the value to another field -- it would just be a long series of custom formulas.