Syncing custom field with excel sheet or separate project file? RRS feed

  • Question

  • I'll try to be short:

    Our shop tasks have codes that the time and estimates are assigned to. The estimated remaining time is updated daily with time card entry. I'd like to import that into excel into a custom column without having to go through and type it for every job and every task, every day. Can I assign a 'cell' in project to link to an excel or access file? Say for Job #4555, task S-15, I would find all of the S-15 tasks for that job in my project file and link them to the corresponding cell in the excel report that we get.

    Thanks you

    Tuesday, November 25, 2014 2:11 PM

All replies

  • Werdnaegni,

    Are you working in a Project Server environment? If so, I'll let someone else field this one as Project Server may have better ways to get the data you want.

    If you are not using Project Server, it's not quite clear whether you are trying to get data into Project or into Excel . For example the first part of your post says you want to import time card data into Excel but then later it says you want to link Project field cells to corresponding cells in an Excel report. Please clarify.

    The basic answer is yes, you can "link" Project to Excel but the best way to do that depends on clarification of the above.


    Tuesday, November 25, 2014 4:12 PM
  • Sorry for the confusion. The data that I enter is already exported into excel. I want to move that data to Project.

    Thanks for the reply. Oh and it's not Project server.

    Wednesday, November 26, 2014 2:14 PM
  • Werdnaegni,

    My first thought is to use the import Wizard. However, we need to understand how the time is stored in Excel. First, I assume by "remaining time" you mean remaining work (i.e. not duration).

    It isn't quite clear if there is just one value of time in Excel to be spread to all tasks in Project that have the particular job code, or whether there is a one-for-one pairing of time data in Excel to be imported into each project task for that job code. If there is a one-for-one paring, (e.g. 3 time data entries in Excel that must be imported into 3 separate project tasks), then the import wizard should work fine. I would use the Unique ID field as the merge key, which means you will need to have the Project task unique ID value as a column in Excel.

    However, if there is only one time data entry in Excel that must apply to multiple tasks in Project, then the import wizard won't work for you. You will need to define how the data is to be apportioned among all the project tasks for the particular job code and then you will either need to copy and paste that into Project, or use the better approach of a VBA macro to transfer the data. Note that the macro code could include the apportioning factors.

    So, this could be fairly straightforward, (i.e. import Wizard), or a little more complex (i.e. VBA).

    Note, if you are tempted to use paste linking to tie the data between the two applications, forget it. It could work, but you are asking for corruption, which is pretty much guaranteed with linked files.


    Wednesday, November 26, 2014 3:33 PM
  • I'll lay out an extremely simplified version of what I'm after.

    In project, I may have something like:

    JOB    |  Task   |Hours used (the thing I want from Excel) |

    Sometimes there may be 2 lines within the same job # that have the same task if two departments share a task at different points within the project.

    If it's really hard to make it apply the "hours used" from one task to multiple things, I can change the way we do it and just say "Sorry, too bad, hours used is only going to one of those" and they can deal with it.

    Would I have to make a new column that contains both the job AND the task? I have a column for job #, and one for task...can I make the import wizard check for BOTH of those when applying data to the new 'hours used' column?

    Thank you for your help, and I hope I've explained enough. I think what you said makes sense, but I'm trying to make sure I understand.

    Wednesday, November 26, 2014 6:10 PM
  • Werdnaegni,

    are there resources assigned to the tasks in Project? How about task type (i.e. fixed units, fixed work, fixed duration)? Are you using auto-scheduled or manual scheduled in Project?

    Tell us more about the "2 lines within the same job #". Keep in mind that every task description within a given project file should be unique. So you can either have two resources, (e.g. different departments), assigned to a single task, or two separate tasks, (e.g. Design interface - Electrical and Design interface - Mechanical), with a single department resource assigned to each. The two separate tasks may or may not be in parallel.

    It's not that it is necessarily hard to apply hours to multiple tasks, but if the data input to Excel, say from a timekeeping system, is a single value, then you would need some type of allocation factor to apply those hours to more than one task in Project. Telling someone "sorry, too bad" sounds like the effort to do it right isn't a top priority. What exactly is the end goal?

    When using the import wizard, you can only select one field as the merge key. That's why I suggest you use the Unique ID field.


    Wednesday, November 26, 2014 8:28 PM
  • I think that answers my question. The one merge key note, that is.

    There are resources assigned, but the task codes, which weren't decided upon by me, cover a few different actual tasks. Say, machining something, and then assembling that something later on in the project. It wouldn't make sense to have them as the same task since they happen at totally different points in the process, but as far as coding goes for our estimates, that's what they use for both parts of that task and what the guys write on their time cards, so that's what's entered. I tried to get them to create more specific codes, but people here aren't big on change.

    I wouldn't mind having the total remaining hours applied to multiple tasks, and not even divided up. It's really just a column to show them where they are with that task code as it relates to the estimated hours, and they all know that the remaining hours in some situations aren't JUST for their part of the task code.

    The issue I'm guessing I'll run into, is that since our time information is exported as an excel sheet with both a job number column AND a task code column, I may have a problem, because (again, not my decision) our entire project file contains ALL of our active jobs. That means that I can't just apply S-15's remaining horus to the whole project file, because job #4550's S-15 remaining hours are totally different from those of job #4579.

    Hopefully that all made sense. Do you still think it's possible given that, or will I have to find a way to change how the excel information is exported (and make it use UNIQUE codes, like you said, such as 4550-S-15 or something)?

    Thank you for the help.

    Wednesday, November 26, 2014 9:07 PM
  • Werdnaegni,

    I wrote a followup response last night but for some reason the forum server didn't "capture" it. Nuts.

    Okay, first off, what you are describing as "task codes" sounds like it is a Work Breakdown Structure (WBS). Project automatically generates a WBS but it is user editable to meet specific user needs. If not a WBS, then your task codes are probably charge numbers. Charge numbers are individual numbers created within each WBS element to track costs either by function (e.g. electrical, mechanical, software, etc.) or by department (e.g. design, fabrication, test, etc.). A charge number is what an individual enters on their time card. Time card data is gathered each period and "dumped" into the various WBS "buckets" to track program costs.

    Here's the part that's confusing. You talk about time card data and about remaining work. They are totally different things. Time card data is actual work. Remaining work is based on an estimate by the performing individual or organization as a measure of how much more work is required to complete the task. Unfortunately remaining work is sometimes "decreed" by management but that is a mistake. So there should really be two parameters that might be imported into Project from Excel, actual hours by resource per task and remaining work estimates per task. It makes no sense to import either of these data metrics on anything other than a task by task basis.

    I'm assuming your project structure shows summary lines as the various job numbers. Under each job, there are multiple performance tasks, (i.e. tasks with resources assigned), and each task is part of a task code. If task codes are broken out by department and not be job, then a given task code will appear on tasks under more than one job. That's how I understand your Project structure.

    In Excel it sounds like the data is simply grouped into task codes under each job, so there is a mismatch of the data between the two applications. If you want to import the Excel data into Project, and have it be meaningful and useful, you should match up the Unique ID field, (a field that is generated by Project), with the Excel data. This will require the Excel task code data to be expanded by allocating the total values among all the unique IDs (i.e. performance tasks) from Project. Then you can use an import map to import data from Excel to Project using the unique ID as the merge key.

    What you didn't really tell me thus far is your end goal? What are you trying to do and to what purpose? Once we understand that, we'll figure out the how.


    Thursday, November 27, 2014 4:35 PM