Brand new in 2021.5

 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 reference in a single Excel spreadsheet.

 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 imagination limits the possibilities. (If you are the only one making entries, the simpler and 'plainer' the better. If you are sending to 3rd parties for completion, this is where 'fancier' may be better.)

 Caveat: This project is aimed at Excel afficionados. If you don't use Excel, this aspect of Pathagoras is not for you. Further, you must be familiar with Pathagoras essentials, i.e., variables, !groups! and <<*Options*>> and <<*Optional*>> blocks. This is definitely not an 'entry level' feature for the administrator. (But a well designed Excel Complete project will allow beginners for whom a project may be  created to assemble and complete documents with little familiarity with Pathagoras.)

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

     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.

(Column

(Column B)

FileName

I Love You Will

FileName

Notary Jurat

FileName(new)

POA

!sex of testator!

1(2)

!num children!

3

!minor Children!

True

[Testator Name]

Johanna Doe

[Spouse Name]

Joseph Doe

[Child@1Name]

Johnny Doe

[Child@2 Name]

Jannie Doe

[Child@3 Name]

Jaimie Doe

[Personal Representative Name]

=[Spouse Name]

[Alt Personal Representative Name]

Johnny Doe

 

(Coloring above is for illustration and reference to comments at right only. Coloring not required)

FileNames (The word 'FileName' must appear in Column A) You can reference a single document that is a 'complete' document; a document contains <<document calls>> to a series of other documents; or you may have multiple FileName entries in separate Excel rows. Click here to be taken to the bottom of this page for a tip to quickly and accurately insert file name(s) in Column B. To create a new document (instead of adding text to the end of the previous document call), add "(new)" after "FileName".

!GroupName! Groupnames control conditional text. If you know the protocol, you can preset values on this page, but doing so is totally optional. If you know how to use them, we recommend setting them here. But if you don't, not to worry  If not set here, the conditional text will be processed after the document is assembled in the normal Pathagoras fashion.**

 

[Variables] List all your [variables] last. Variable name (brackets optional), but double check your spelling.

====================

All column B items can be manually completed or completed with Excel completion tools
(e.g., validation lists, Power Query, 'equal' formula, etc.).

 

**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.

 

If using Named Cells:

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)

 

   The more you know about Excel, the quicker you can make the process assigning values to the various cells in the spreadsheet. Other than the 'look' of the final product as described above, there are no restrictions that need to be kept in mind for Pathagoras purposes. If you know how to use vlookup and hlookup, simply point and click to assigned and desired values. If you know how to create cell equivalencies, go for it. If you know how to name cells, you can used the names cells, and the data they contain, to drive the process. Even more power, and even more elegance.

 

   Keep in mind also that Excel Complete is totally optional. And absolutely nothing in your Word documents needs to change to take advantage of this feature.

GENERAL:

Referencing a document:

The document(s) may be fully qualified (i.e., drive, folder, sub-folders, full document name) or with just its short name. If using just a short name, the called document must be in the 'hunt path ' (generally a DropDown List, but it can be other places that Pathagoras will look). First found document during a 'hunt' is what is called. If the document will be shared using DropBox, OneDrive, etc., consider using the path 'shortcut' [DBP].

Of course, you can manually type a document name in the cell. But the beauty of this routine is that you can use any of Excels many tools to feed a value into the 'document to be called' cell.

You can call as many documents as you wish. Just use additional lines or named cells. However, don't forget about 'clause sets'. A clause set is a single document that contains calls to additional documents. Check out this page in the Manual. So, let's say you have clause sets for a simple 'I Love You Will ' which contains clauses from your Will clauses collection, just call the I Love You Will document and Pathagoras will take care of the rest.

 

Referencing a !groupname!. 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: Brackets in Column A to denote that the term is a variable are optional, but a best practice. Values should be written in 'normal' style, i.e., names, Upperlower Upperlower; for other values in the way they would typically appear in the body of the document.

 

Listing Files and Variables with advanced Excel tools:

   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 aldvanced 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.  Selecting files for inclusion in your Excel Complete project could not be easier or more accurate.   (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.

tipIf 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.