Andrew Makar developed an import map that will properly import your vendor's Excel sheet that builds the task hierarchy in Microsoft Project. ————————————————————————————————————————————————— For the past four years, I've worked for companies that outsource the majority of their IT work to external vendors using. One challenge in jointly delivering a project with an external vendor is obtaining the vendor project schedule in a format that can be integrated with Microsoft Project. The obvious solution is to have the vendor provide its Microsoft Project schedule; the reality is some vendors are reluctant to hand over their detailed schedule because it contains cost data, notes, custom macros, and other private data.

On step 3, import your data into a new project or the project you have open, and click Next. On step 4, pick the type of information you're importing so the wizard can map it from Excel to Project, and click Next. On step 5, verify the mapped fields, make adjustments as needed, and click Next. The entire Microsoft Office 2016 suite for Mac includes Excel, PowerPoint, OneNote, Word and Outlook, though it sounds like you might only be looking for the word processor piece of that (Microsoft Word).

If you've worked with outsourcing vendors, then you're familiar with some vendors who don't consistently use Microsoft Project as a scheduling tool. In my case, I typically receive an Excel file with tasks and start and finish dates. Ironically, my vendor extracts this information from his Microsoft Project schedule and provides an Excel file that I can scroll through to find key milestones and due dates. Faced with the poor usability in scanning hundreds of tasks using Excel, I developed an import map that will properly import the Excel sheet that builds the task hierarchy in Microsoft Project. When you export data from Microsoft Project into Excel, the data file doesn't maintain the hierarchy.

Creating the hierarchy in Excel usually involves grouping and indenting in Excel or using a custom macro to build the hierarchy. When you import an Excel file into Microsoft Project, it also lacks any of the indenting ( Figure A) and summary tasks that make Microsoft Project a valuable roll-up tool. Microsoft Project Excel import without task hierarchy. (Click the image to enlarge.) Building the import map My solution was to develop an import map that includes the key fields in Figure B. Figure B Field Name Description ID The Task ID for the Microsoft Project task Outline Level Determines the Outline Level in a project's hierarchy. An Outline Level of 1 is at the highest level in the hierarchy, and an Outline Level of 5 has four summary level tasks above it. Name Task Name Start Forecasted Start date Finish Forecasted Finish date% Complete Task completion percentage Baseline Start Original Baseline Start date Baseline Finish Original Baseline Finish date Actual Start Actual task Start date Actual Finish Actual task Finish date Predecessors Identifies the Task ID of a predecessor task Resource Name Assigned Resource Export field names To build this map in Microsoft Project, follow these steps: 1.

Open a sample Microsoft Project schedule. (It helps if you have a completed project schedule so the final export will have meaningful data.) 2. Go to File Save As. Select the Microsoft Excel Workbook (*.xls) as the Save as Type and click Save. Click Next and leave Selected Data as the option. Click New Map.

Select the Tasks checkbox ( Figure C). Figure C Export Wizard - Task Mapping 7. Click the Microsoft Office Project field and select the fields in Figure B. Click the Next button. Click Save Map and Save It as Excel MPP Map. Click the Finish button.

The Excel extract will now contain the key fields needed to build the project hierarchy. In this case, I had to build the export map for the vendor so they could simply export their Microsoft Project data into a format that I could use to import the file.

Once the vendor had this map in their Microsoft Project file, the vendor could easily save an Excel file using this extract. It ensured the vendor's confidential data was kept confidential, while the critical data that I needed to understand milestones and start and finish dates for key tasks could be imported into my Microsoft Project schedule. Once the vendor provided a file using this format, their schedule could easily be imported into Microsoft Project by following these steps: 1. Start Microsoft Project with a blank project schedule.

In Microsoft Project, go to File Open. Change the Files of Type combo box to Microsoft Excel (*.xls). Select the extract file and click Open. Click the Next button.

Select Use Existing Map. Select the Excel MPP map. Select Append the Data to the Active Project ( Figure D).

Figure D Import Wizard - Import Mode 9. Click the Next button.

Click the Next button. Click the Finish button. The end result is a properly formatted Microsoft Project file that contains the vendor's project schedule.