Converting Excel Formula To MS Project Custom Field?
Mar 20, 2014
A coworker is setting up an MS Project...project, and adding a custom field that would convert the Start date field into our Fiscal Quarter Fiscal Week schema, to display as FQFW, e.g. Q1W1. I've written the formula in excel, but I've never even touched Project until this week. Apparently it doesn't take formulas quite the same way as Excel does. I tried using the ''Switch" function in Project, and it worked, but it only accepts 14 arguments, and there are obviously 52 weeks we're dealing with. Would there be a way to do this using VBA? (I know next to nothing here as well.)
Formula where A1 is the Start Date:
Code:
=IF(AND(A1>=DATE(2014,2,1),A1=DATE(2014,5,3),A1=DATE(2014,8,2),A1=DATE(2014,11,1),A1
View 2 Replies
ADVERTISEMENT
Sep 5, 2013
My task is to consolidate 4 Excel Project Lists (Workbooks) to a Master Workbook. The Project Lists has a different structure and almost different content. The relevant information is always on Sheet1 but it has completely different ranges. The only constant is the Project Number, which should be used to sort the information. Every Project should be listed only once with all the existing information.
I found a code written by Ron de Bruin which has already some components that I want to have in my VBA but I think there are still a lot of necessary adjustments to do.
Code:
Sub MergeSelectedWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim SelectedFiles() As Variant
Dim NRow As Long
Dim FileName As String
Dim NFile As Long
Dim WorkBk As Workbook
[code]....
The Master Project List should has the headers in Row1 and the information listed below. The Macro should automatically places the correct information to the correct column. Some of the information are in 2 or more of the lists but they should be listed only once in the Master List.
Project Number
Project Description
...
1111E.000000001
[code]....
I guess a problem is that the structures of the Lists are quite different so there must be a kind of sorting process.
In the end I want to have an Excel File with the Macro and a Command Button and by clicking the Macro creates a new Workbook with the Master List.
It would be better if there is a variable range instead of a defined. Like the Macro searches the last row and starts at this row and column.
View 4 Replies
View Related
Jul 30, 2013
I am trying to get excel to open a MS Project file and then run a project macro on it.
What is happen is that when I run my code it works, and then excels VBA window freezes up and the run arrow goes away, and if I try to open project I just get a chiming noise and it won't open.
For now I have to select the MS Project file from a directory, but in the future I would like it to go through the folder and open all of the file in the folder
EXCEL CODE
Sub ImportMSProject()
Dim FileToOpen
Dim mpApp As MSProject.Application
Dim prjmacro As Object
'Identify the File to Open - START
FileToOpen = Application.GetOpenFilename("Microsoft Project Files (*.mpp), *.mpp")
If FileToOpen = False Then
Exit Sub
End If
'Identify the File to Open - END
[code].....
View 9 Replies
View Related
Jul 10, 2014
I'm trying to modify a worksheet and I'm having mental block. Basically I have the A:N columns full of data, and about 40 helper columns after that. All the helper columns being referenced by many macros.
I now have to add one more data column after the N column, so the helper columns will be cut and pasted one cell to the right.
The question is, how do I efficiently modify the code that looks at the helper columns to take into account the "1 field to the right" movement of the helper columns? Here is an example of the code
[Code] .....
There are tens of references across the project for every helper column so doing it manually is impossible.
View 2 Replies
View Related
Jul 24, 2014
I have a list of 600 unique project IDs in column A. I want to create a second list in Column B that references the contents in Column A, first row then duplicates that item for two subsequent rows, then a formula in the fourth row of Column B a reference to the second project ID in Column A row 2, with two duplicates in the fifth and sixth row.
Column A
Row 1 000891.AB
Row 2 000892.CD
Need to create:
Column B
Row 1 000891.AB
Row 2 000891.AB
Row 3 000891.AB
Row 4 000892.CD
Row 5 000892.CD
Row 6 000892.CD
View 2 Replies
View Related
Jan 22, 2014
I have attendance tables that has column Y showing the shift to be worked and column Z showing any absentees. I used the following formula to count the number of people off per period.
{=SUM(COUNTIFS($Y$5:$Y$29,AM,$Z$5:$Z$29,$W82),(COUNTIFS($Y$36:$Y$60,AM,$Z$36:$Z$60,$W82)))}
in the above formula AM relates to a list of morning shifts.
The problem I have now encountered is that the system at work is still using excel 97 - 2003 which does not recognise Countifs.
way to adapt the formula to be compatible with Excel 97.
View 7 Replies
View Related
Jan 22, 2014
I have a pivot table that analyzes test results. Assume three fields: Test, Result and Failure. If a test resulted in a failure then there is 1 in this field; otherwise there is a 0. There are several test results for each test. I want to calulate the percentage of failures for each test. I have selected Test for Row Labels. I cannot figure out how to calulate the percentage. Basically, the formula equates to =SUM(Failures)/COUNTA(Test). In other words, for each test add the failures and divide by the total number of results for that test.
View 2 Replies
View Related
Jul 10, 2014
I am using excel to compare data from one spreadsheet to another. However my utility is not capturing identical matches since one worksheet eliminated the leading 0s while the next worksheet includes all 0s. Our system should actually have the 0s to identify the data correctly.
Worksheet 1
M10235
Worksheet 2
M000010235
I would like to convert Worksheet 1 to show M10235 in the format M000010235 so that the number contains the prefix M followed by nine digits.
View 2 Replies
View Related
Aug 13, 2013
I am migrating a bookkeepping spreadsheet from Quattro v12 (c2001) to Excel 2007 and have hit a stone wall over relative 3-D references.
In this workbook each sheet holds data for one month, and this includes some lagging-12-month calculations -- that is, summing a range on a single cell for the preceding 12 sheets (including this one).
Unexpectedly, this formula:
=ISUM('201206:201305'!M6)
gives absolute references, even though I put in no dollar signs after the sheet names. This means it gives the wrong answer when copied to the next sheet to start a new month.
I can find no reference at all to converting the above formula from absolute to relative reference.
View 5 Replies
View Related
Oct 6, 2009
Is there a way to apply a custom validation rule to a drop down field in Excel (which itself is already using a validation rule)? Here is what I wish to do: I have a range of cells that can only pick up values from a list. I want to further apply a custom validation rule to these cells - display an error message when one chooses an element from the list that has already been used by another cell in the range.
View 2 Replies
View Related
Feb 13, 2013
I have a simple formula ='info page'!b2&'info page'!b5 in places that on my sheet adds a company prefix to a item number, prefix in b2 and item in b5, we have a client that we cant use prefixes but now when I leave the field blank its giving a zero and thats not good either. I'm pretty sure an IFERROR or something will work but can get the syntax right.
View 5 Replies
View Related
Apr 30, 2014
I often use the same file/pivot table for both month and weekly reporting. In my source data my field headers show 1,2,3,4..etc. This way I can use for month or weekly. But I often change the "Custom" name on the Field Value Settings to something more descriptive like "Jan" or "Week1"
The problem is that each time I update the the "Custom" name the Pivot Table auto refreshes and it takes forever to re-label all the columns.
Is there a way to suppress this refresh when updating the Custom Field Name?
Capture.JPG
View 2 Replies
View Related
Feb 15, 2014
I'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
View 9 Replies
View Related
Aug 4, 2014
I have a table of projects with 1) duration in year, 2) time window (number of years of our planning cycle), and 3) start year of the project. I want to generate a list of project parts of all projects where they may take place. This will serve as an input to an optimization program.
So a project of 2-year duration should have 2 parts over any year within the time window. I am including the "impossible" ones for my developer to tag them as "0" when we run it through his code.
View 5 Replies
View Related
Apr 27, 2007
I have tried to record macro to protect VBAProject / lock project for viewing. But can not success by that way.
View 9 Replies
View Related
Oct 21, 2011
If i have a table full on excel data, date, names etc, and I have a userform where someone has filled all that in. On that particular userform I was wondering if their's a way I can get it to write each column over to a sample MS project document.
To save me having to manually change it over?
so on clicking a button, what ever's in sheet1 say it writes out out to specific ms project strips, e.g. start date(excel) written out to data(project)
View 8 Replies
View Related
Nov 24, 2013
Uses excel 2010 to track the project in the form of project status report. Basically it has to look simple yet tracking should be in the form of progress bar etc.. Our aim is to track a 3month project.
View 1 Replies
View Related
Apr 28, 2014
Writing a macro in excel where you can use the data in your excel file to copy data to microsoft project.
I have an excel file of task name, deadline, time remaining, and assigned to and I want to copy those 4 columns to a correlating column in project. So basically I would loop through these fields on each line til I reach a blank which could be entering a varied amount of tasks.
The full macro would change ms project start date to today's date then copy the 4 columns from excel to project to add a new task for each line in my file and then save the project file.
I've seen some links on macro with project but most are more advanced then this or are exporting data from project to excel.
View 1 Replies
View Related
Aug 30, 2012
I'm a basic user of excel 2003. I recorded a macro that copy a teacher absent schedule to a daily cover schedule as follow:
Sub ABS_M1()
'
' ABS_M1 Macro
' Absent teacher monday 8/29/2012 by Oscar
' Daily cover schedule
'
Range("A65:J67").Select
Selection.Copy
Sheets("Covers").Select
Range("B5").Select
ActiveSheet.Paste
End Sub
Using the same VBA: What function should I use to make the Sub ABS_M1 move down 4 rows to a new range on the cover sheet if the first Range ("B5") is already used and so on?
View 4 Replies
View Related
Aug 15, 2013
Why does this code no longer work? It gives me the error code Cannot find project or library and MyMsg = is highlighted in blue. This worked in 2003, but does not seem to like 2007 version of excel.
Sub Send2()
'This is the "Send to XX" button
MyMsg = "Did you remember to name and save this file to your computer?"
Response = MsgBox(MyMsg, vbYesNo, Attention)
Select Case Response
Case Is = vbNo
[Code] .....
View 9 Replies
View Related
Jun 8, 2014
Is there any Software / Excel Add-in to provide VBA Project Documentation? I'm looking for something that can tell me which code does what? Which codes and functions are related. What are the references (with Other MS Office products / foreign-objects if any). How many codes and code names
View 8 Replies
View Related
Jan 9, 2009
i'm using a macro in excel 2007 that will open a Microsoft Project file, and go to the filtering menu. This is working fine however i have come to a deadend as i need the answer from a Data validated menu to be inserted into a box that project creates. Basically i need to copy the text from my excel (worksheet 2, cell c2), into my vba formula so that it automatically inserts into the MS project box. I hope this all makes sense and would be massively gratefull if you could point me in the right direction.
View 9 Replies
View Related
Aug 28, 2007
I am making a 'sports day' spreadsheet for a project and i have one problem, the spread sheet is meant to have the possiblitlity of mistakes reduced to a minimum.
I have 6 teams in the sports day and under each i put 1st, 2nd,3rd,4th,5th,6th.
[url]
basically i need the rows to only accept 1st 2nd 3rd..... only once. I need something like an error message to come up if a value is entered twice on a row.
View 12 Replies
View Related
Aug 28, 2007
I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].
View 4 Replies
View Related
May 25, 2014
It works fine with several versions of Excel (2003, 2010, and 2013). But the problem arises when I move from Windows XP to Windows 7 or 8.
I use a scanner to scan barcode serial numbers into a textbox, and then populate these serial numbers with other associated data into a spreadsheet. The serial numbers are supposed to be separated by each line and placed into an array.
I get a compile error "Cant find project or library" with CHR highlighted in blue from this line of code...
SNs = Split(Str, Chr(10))
Once again, it works fine on many machines with XP, but not on Win 7 or 8.
[Code] .....
View 4 Replies
View Related
Mar 4, 2014
One of our employees has wrote a VB project in excel 2010 which works on some machines and not others, it comes up with the below error message.
I have checked VB and there are no missing references.
Compile Error: Can't find project or library
Private Sub UserForm_Initialize()
'Empty ClientCodeComboBox
Me.ClientCodeComboBox.Clear
[Code] .....
View 14 Replies
View Related
Feb 8, 2013
I have got an issue. in MSE 2003, this beginning of macro worked:
Sub Consolide()
MYFOLDER = "D:DATAMypath"
In MSE 2010, it gives me an error: "Can't find project or library"
I really know that my path is defined fine, because it worked perfect before MS update.
View 1 Replies
View Related
Jun 29, 2014
I have the below code which was working at work using Excel 2010. We have upgraded to Office 2013 and it no longer works
Error: Compile Error. Cant find Project or Library
It does highlight this : If Right(varFile, 4) = ".xls" Then
Code:
Sub PropertyName_SAVEAS()
Sheets("Menu").Select
Range("I27").Select
Dim varFile As Variant
Dim strFileNameDefault As String
On Error Resume Next
[Code] .......
View 2 Replies
View Related
Dec 9, 2013
Attached is a spreadsheet wherein I'm trying to extrapolate project costs across various months based on working days in a month subject to start and end dates of the project. Need an accurate formula to spread the cost.
Days & Cost Allocation Example.xlsx
View 2 Replies
View Related
Nov 5, 2012
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
View 3 Replies
View Related
