D Using The Excel Template: Fill & Download for Free

Single most underutilized trick, that I have almost never seen anybody use, is the shortcut Ctrl+D.Ctrl+D seemingly copies the contents of the cell immediately above the current cell to the current cell. But that’s not all it does. If the above cell contains a formula with references to other cells, Ctrl+D will not simply copy the contents, instead it will copy the formula with all references updated and will display a result.I have seen people working on Excel in high paced jobs where they had to keep repeating the same thing over and over again in a column, like a date, and they were leaving the cells empty to fill at the end of the day at once because repeatedly typing the entire date took too long. They were shocked when I told them how they could save time by using Ctrl+D.Update:A commenter mentioned, in a rather cryptic manner, that if you select a column of cells where first cell is not empty, Ctrl+D will work like fill handle being dragged down and it will use the top cell as the template, will update references if a formula is found.I have checked. It works. Same thing works for Ctrl+R too if you’ve selected a row. Contents of left most cell will be used in this situation.One exception to note is that fill-handle can guess a data series to fill by using data in first 2 or more cells. Ctrl+D and Ctrl+R simply copy data and formulae, they do not guess data series.

Normally, I would do this kind of thing by loading the Excel data into an array, then using the array to write the Word document. Therefore, I would use a Word macro.Update: I just noticed that you wanted one Word document per Excel row. I’ve updated my sample code to handle that.Here is the information from my test using Office 2007.In the VBA editor, I used the Tools > References menu option to add a reference to the Microsoft Excel 12.0 Object Library.At the top of the module in VBA, I added the following declaration to use 1, instead of 0, as the starting index of an array. (This is not entirely necessary, but it is relevant to my sample code.)Option Base 1 Here is my sample code for my main procedure:Sub TestLoadingExcelData()  Const TemplatePath = "C:\Users\Rupert\AppData\Roaming\Microsoft\Templates\New_LPD.dotm"  Const SpreadsheetPath = "D:\Rupert\Documents\MyTimeLine.xlsx"  Const targetPath = "D:\Rupert\Documents\tmp\"    Const SheetName = "Sheet1"  Const NumberOfCols = 12  Const StartingRow = 2  Const MaxRows = 500  Dim TwoDimArray(MaxRows, NumberOfCols) As String  Dim i As Long, j As Long  Dim tmpText As String    Call LoadDataFromOneSheet(SpreadsheetPath, SheetName, NumberOfCols, StartingRow, TwoDimArray, MaxRows)    'Type the text from the array.  For i = StartingRow To MaxRows  If TwoDimArray(i, 1) = "" Then Exit For  Documents.Add Template:=TemplatePath, NewTemplate:=False, DocumentType:=0    'Go to the starting point to write text in the template. I'm using some text that I happened to have in my template.  Selection.Find.ClearFormatting  With Selection.Find  .Text = "This is the built-in Heading 2."  .Replacement.Text = ""  .Forward = True  .Wrap = wdFindContinue  .Format = False  .MatchCase = False  .MatchWholeWord = False  .MatchWildcards = False  .MatchSoundsLike = False  .MatchAllWordForms = False  End With  Selection.Find.Execute  'Delete the placeholder text.  Selection.Delete    For j = 1 To NumberOfCols  tmpText = TwoDimArray(i, j)  If j = 1 Then  Selection.TypeText Text:=tmpText & vbCr  ElseIf tmpText <> "" Then  Selection.TypeText Text:="---" & tmpText & vbCr  End If  Next j  ActiveDocument.SaveAs FileName:="""" & targetPath & TwoDimArray(i, 1) & ".docx"""  ActiveDocument.Close  Next i  End Sub Here is my sample code for the procedure that loads the array from Excel. I use a separate procedure for this to isolate errors that may occur in the Excel application.Private Sub LoadDataFromOneSheet(fSpreadsheetPath, fSheetName, fNumberOfCols, fStartingRow, ByRef fTwoDimArray, fMaxRows)   On Error GoTo ErrorHandler  Dim tmpText As String  Dim i As Long, j As Long, emptyRowCount As Long    Dim wbXL  Set wbXL = CreateObject("excel.application")  wbXL.Workbooks.Open FileName:=fSpreadsheetPath  Dim MyWorksheet   For Each MyWorksheet In wbXL.Worksheets  If Trim(MyWorksheet.Name) = fSheetName Then  For i = fStartingRow To fMaxRows  If SmartRTrim(CStr(MyWorksheet.Cells(i, 1).Value)) = "" Then  'Quit when you reach an empty cell in the first column.  Exit For  Else  For j = 1 To fNumberOfCols  fTwoDimArray(i, j) = SmartRTrim(CStr(MyWorksheet.Cells(i, j).Value))  Next j  End If  Next i  End If  Next MyWorksheet    wbXL.Workbooks(1).Close  wbXL.Quit  Set wbXL = Nothing Exit Sub ' Error-handling routine. ErrorHandler:  wbXL.Quit  Set wbXL = Nothing  MsgBox SpreadsheetPath & vbCr & Err.Description End Sub Here is the function that I use to trim extraneous control characters from Excel cell text:Public Function SmartRTrim(fstring) As String  'Remove control characters and spaces from the end of the string.  Dim tmpString As String  Dim i As Integer    tmpString = fstring  For i = 1 To 300  If tmpString = "" Then  Exit For  Else  If Asc(Right(tmpString, 1)) < 30 Or Right(tmpString, 1) = " " Then  tmpString = Left(tmpString, Len(tmpString) - 1)  Else  Exit For  End If  End If  Next i  SmartRTrim = RTrim(tmpString) End Function 

The procedure for designing a DACI matrix:Another version of the RACI matrix, that has been used to centralize decision making, and clarify who can re-open discussions, is the DACI matrix.By going through each process step (activity) you can appoint several responsibilities (D/A/C/I) for each role involved. A discussion about this, will make sure everybody understand it’s part in the process, which will make the work go much smoother.D Driver : a single driver of overall project like the person steering a car.A Approver : One or more approver(s) who make most project decisions, and are responsible if it fails.C Contributors : Are the worker-bees who are responsible for deliverables; and with whom there is two-way communication.I Informed : Those who are impacted by the project and are provided status and informed of decisions; and with whom there is one-way communication.The DACI matrix is based on the RACI matrix. Both are 'Responsibility assignment matrices’, which is a is a very powerful tool to ensure all roles in a company/process are aligned.Please note a role doesn’t necessarily means a job position. It can be different. The RACI matrix is a diagram which is used to describe the responsibilities of various roles in project tasks or deliverables. It's an abbreviation for:Responsible: Those who are responsible for the implementation of the task and achievement of the deliverable. There is commonly one role with a participation type of responsible, although others can be delegated to assist in the work that is requiredAccountable (also known as: Final Approving Authority or Approver): There can only be one person who is ultimately accountable for the correct and thorough completion of each task or deliverable. The Accountable is the person who must sign off (Approve) on the work that the Responsible provides. Therefore, the Responsible is accountable to him/her.Consulted (sometimes Counsel): Those whose opinions are required; and with whom there is two-way communication.Informed: those who are kept up-to-date about the decisions on the development, achievements of the task or deliverable; this is only one-way communication.There are a number of alternatives to the RACI participation types which are also used: RASCI model (RASIC), RACI-VS, RACIO, DACI, CAIRO, etcYou can easily work out a DACI matrix with a template, for example in this IBM Excel template: DACI matrix.