New in 2022

 For want of a better name, we are calling this Excel Complete. It references the fact that all elements of a complete document assembly project can be referenced in a single Excel spreadsheet. By every element, we mean:

odocument(s) selection,

oconditional text assignments (e.g,. 'are there no, one or multiple children?"; is 'NDA 'company' or 'second party' friendly?'; is the POA 'springing' or 'effective immediately?'"; pronoun selections; etc.)

ovariable values ([Client Name] = JoAnna Doe, etc.)

 The spreadsheet can be as simple as a two column 'plain text' display of the document's elements. Or it can be a fancy spreadsheet with named cells, links and tables. Only your imagination limits the possibilities. (If you are the only one making entries, a simpler and 'plainer' approach may be better. If you are sending to 3rd parties for completion, this is where 'fancier' may be better.)

 Caveat: This project requires competence in using Excel and familiarity with Pathagoras essentials ( variables, !groups! and conditional text). There are many terrific tutorials that will teach you how to create the 'fancy' spreadsheets we mention aboveand of course this Manual and associated videos discuss in detail the Pathagoras tools mentioned. If you don't use Excel at all (or yet), this aspect of Pathagoras is not for you.

     The elements and requirements of an Excel Complete spreadsheet are these:

     Type #1: Simple 2 columns:

The first row(s) must reference the desired document and column A in that row must contain the word "FileName' or "File Name" (case is not critical. It can be 'filename'). Column B would contain the name of the desired Word document to be called. See footnote as to requirements for the reference. You can reference as many documents as you wish. Just type "File Name' in column A and the document name in column B.

If you are setting conditional values via !groupnames!, the next 'row(s)' would list the !groupname! (column A) and its value ((Column B) !groupnames! must be written with opening and closing exclamation marks.

 

The next rows contain the variable names and their respective values.

 

**The values for !group names! are either numeric or Boolean.

  '1(3)' means the first choice of 3 elements of an <<*Options*. . .>> block..  Likewise, 3,5,6(9) would select choices 3, 5 and 6 of 9 possible choices of an <<*Options*. . .>> block.

 If a single number is used, that reflect the value of a <<*Repeat*. . .>> block.

 A 'True' or 'False' value reflects a a 'keep it' (or 'delete it') decision for a particular <<*Optional*. . . >> block.

 (Remember, Pathagoras uses positions and not values to effect a action in your document. The 'answer' for one block of conditional text is often not the same answer for a different block further down in the document. But, it properly constructed, the position of the initial answer will always give the proper result for other blocks in the same group.

 

     Type #2: "Fancy" (using Named Cells and/or other advanced tools in Excel):

Filenames: The name(s) of the cells containing the document call(s) must start with 'FileName'. If you are calling more that one document, you can add a suffix, e.g., Filename1, Filename2, etc., to the named cell.

!Groupnames!: If you are setting conditional values using !groupnames!, remember that a cell name cannot contain exclamation marks. Nor can it contain spaces. These are Excel, not Pathagoras, restrictions.) So to indicate a !groupname!, use underscores at each end of the cellname, and replace any spaces with underscores as well. So the Excel cell name for the groupcalled  !number of children! would be created as _number_of_children_.

Variables: The name of the cell should reflect the name of the variable in your document, but without any outside boundary marked (e.g., square brackets). Also remember -- a cell name cannot contain spaces. If your document uses, e.g., [Client Name] to identify your client, the cell name would read Client_Name. (Pathagoras takes care of handling the underscores)

 

GENERAL:

Referencing documents in the Excel Complete table (initial rows, column B):

The document name(s) may be fully qualified (i.e., drive, folder, sub-folders, full document name, with suffix). However, if  the desired document is in Pathagoras' 'hunt path ' (generally a DropDown List, but it can be other places that Pathagoras will look), you can use the base name of the document (you don't even need the suffix).

You can, of course, manually type document name in the appropriate cell. But you can use any of Excels many tools to feed a value into the 'document to be called' cell. (See Advanced Excel Tools, below.

You can list as many documents as you wish. Add as many lines to your Excel Complete spreadsheet as you wish. However, don't forget about Pathagoras' 'clause sets'. A clause set is a single document that contains calls to additional documents. So, let's say you already have created a clause sets for a simple 'I Love You Will ' which contains, let's say, 10 clauses from your Will clauses collection. Instead of re-listing those clauses in the Excel Complete spreadsheet, just list 'I Love You Will' as the desired document. Pathagoras will take care of the rest.

 

Referencing !groupnames!.

The name (Column A) must be indicated by exclamation mark. For the value (column B)Options blocks, provide the position(s) of the desired choice, followed by the number of options in a block of that group. For Repeat blocks, just provide a number. For Optional block, provide a True or False (or Yes or No) value indicating whether Optional text blocks of that group will be kept or deleted.

Referencing Variables:

Using brackets in Column A to denote that the term is a variable is optional, but a best practice. That means you can type 'Client Name' as the name of the variable, but just to remind you that you have started the 'variables' section of the spreadsheet, we still recomment '[Client Name]'. Whatever you choose, the variable should be written in 'normal' style, i.e., in the way they would typically appear in the body of the document. [Client Name] (Upperlower Case); [Date of Signing]; [number of children] (if you want all lower case); etc.

Advanced Excel Tools: selecting files and variables from lists

   While you can manually type or copy and paste the name of any source files you want to assemble or variables you want to list, you can create a list of either without any typing using the wide variety of advanced Excel tools..

  Listing Files:  Check out this video for a simple method to display for all (or a designated sub-set) of files in a folder using Excel's Power Query, an extremely powerful tool that could be helpful in selecting files. (Pre-2016, Power Query was in its own tab. 2016 and later, it's located behind the Data tab and is called "Get and Transform Data".

  Listing Variables: You do not need to guess at the spelling of your variables. You can export variable names into a text or .csv file and, using any Excel tool with which you are familiar, import (or simply copy and paste) them into your spreadsheet..You can create a selectable list of your variables with Power Query. Here is a good video to explain how.

   Setting !GroupName! values: You can manually text Pathagoras which elements of multiple choice !Groups!. Just list the selections, separated by commas followed by, in parentheses, the total number of choices. But for hardcore Excel afficionados, you can create CheckBoxes, named ranges and use Excel's native formulae TEXTJOIN and COUNTA to automatically create the proper selection formula. for any and all multiple choice !Groups!. Write to us an we'll send you a sample.

    A good 'general video' discussing Power Query is here. A good video demonstrating the creation and use of checkboxes can be found here.

 

   The Excel Complete button: Click 'Pathagoras Features' (bottom half) | Forms | Excel Intake Forms. Click that button for access to Excel Complete. Follow the prompts to navigate to the desired spreadsheet.

If you anticipate using this routine, we strongly recommend that you elevate the Excel Intake Forms button to your Quick Access Toolbar (QAT).

 Storing your files: Once you have created your 'master' spreadsheet, you can save your individual client spreadsheets just about anywhere you want.  

 a. The most 'logical' place might be to store 'Complete' files in it in your client's folder. When you are ready to process it you have two approaches. (1) Navigate to it via you Explorer (remember it will be an Excel file). Open it and return to Word and run Excel Complete. Pathagoras will see that the Excel file is open. Select it and proceed. (There is currently no trigger to run the document directly from Excel. This is not our recommended approach).  OR

 (2) Th re file selection routine actually works better if there is no Excel file open. Start the Excel Complete routine. Because no file is open, Pathgoras will ask you to navigate to and select the file you want. NOTE: Pathagoras is programmed to try to open the 'Clients' folder assigned via QuickLinks. If you haven't assigned such a folder, we highly recommend it.Pathagoras takes over, and you can go get a cup of coffee or tea.  

 You can also store your files in a common folder and point a DropDown List to that folder. When you click on the record, Pathagoras will ask if it is an Excel Complete file. It if is, Pathgoras will process it with no additional intervention.