Generally

This topic covers the updating of entries in an existing Excel Workbook, with values contained in an EstimatorVJ Project.

Typically, but not restricted to, this fills in a Client Schedule for Tender submission, using the costs derived within the VJ project.

It also is typically used by  Companies to generate Tender Summary Sheets for the Tender Review process prior to bid submission.

UPDATING THE CLIENT SCHEDULE

Here we have a typical Client Schedule in an Excel Workbook containing GENERIC items for simplicity:






The Procedure

1. Create and Import the data into the Project. The 'Project->Import XLS' option is very handy for this.
   Here is what the Section List and Preliminaries sections look like in the Estimate:
   
   
   
   Some trivial costs have been entered for demo purposes.
2. The Update process occurs by selecting the Project->Print process, then clicking the option "Update XLS" in the "Print to File Options" section of "Print Options" dialog.
3. Start with the "Estimate Summary" option and click "Preview".
   Note: We do not choose the "Detailed Format" as this would generate columns of data not applicable to the format of the Client Schedule
4. The following dialog appears:
   
   
5. We now need to enter the references to the columns of data we are interested in - RATE and COST. Referring back to the Excel 'Summary' sheet above, these are in the Excel columns "E & F" respectively:
   
   
6. Now we need to enter the 'KEY' columns - the columns in the Estimate Report and the Excel Workbook containing the item references - namely the 'CO' column in the Estimate Report and the 'A' column in the Excel workbook:
   
   
7. Click the "OK" button and process begins
8. We get the following Error Report:
   
   
   Why?
   Remember the important fact 2 we mentioned previously:
   
   "The DATA TYPE of these references MUST BE of type "Text". The reason being that Excel gets too clever by half when references appear to be numbers (e.g. 1000), and returns the value "1000.0" when being interrogated by VJ instead of "1000"."
   
   The following image shows that the data in column 'A' is 'General' and NOT 'Text'. This means that Excel is returning back to VJ's request of the data in column 'A' as "1.0", "2.0", and "3.0" which does not match what VJ is looking for.
   
   
   
   
   
   We need to convert these entries in column 'A' to type 'Text' OR change the entries in our estimate to "1.0", "2.0", and "3.0".
   I have not found an easy way of doing this in Excel since even changing the 'Number' format to 'Text', it still returns the references "1.0", "2.0", and "3.0" back to VJ. The only successful way I have found is to Copy, Delete, then Paste back the entries after changing to type 'Text'. The entries then look like this and the report works correctly.
   
   
   
9. Now rerun the report
   CLOSE THE EXCEL WORKBOOK BEFORE RUNNING THE REPORT
10. Now open the Workbook and look at the result:
    
    
11. Confirm the data has been inserted correctly.
12. Notice that the value of 'Total of Tender' in the workbook shows "0.00".
    VJ will only insert the cell data - it will not recalculate as the data is entered for speed reasons.
    YOU NEED TO RECALCULATE THE WORKBOOK.
    
13. Now run the report for the "Estimate Sections" following the same procedure as above for the Summary.
    Note that instead of "CO" we now use "BQREF" for the 'Key column name in the Report'
14. We now get the following Error Report:
    
    
15. These errors are acceptable because if you look at the Excel Workbook displays above, "Item Ref" exists in each of the workbook sheets in the reference column and thus generates the error - this will not affect our report.
    Also you will see the reference "1.2" purposefully existing twice in the "Preliminaries" sheet.
16. Confirm the report has worked - open the "Concrete Work" sheet and it looks correct:
    
    
    
17. Done.

GENERATING TENDER SUMMARY SHEETS

Generally

Commonly, Companies want to view/analyze the finished estimate in a specific format, commonly referred to as a "Tender Summary Sheet".

Here is one of many examples:



In this example, the link between the Estimated costs and their influence in this TSS is via the "Cost category".

The Procedure

The procedure is exactly the same as the "Updating Client Schedule" procedure, except we print a different report and use a different "key" linking the Estimate report and the Excel workbook containing the TSS.

1. Create the TSS proforma in Excel
2. Generate the Estimate, and copy the TSS into the Documents of the project. This makes it easy to locate/Open the TSS as well as storing it within the Estimate backup.
3. Run the appropriate report and make sure it is behaving correctly:
   Below are the options selected in the "Print Options" to achieve our result:
   
   
   
   
   Here is the report it generates:
   
   
4. We validate that this is the report containing the information we need and the data is correct
5. Take a note of the columns we need to use for the source of the data and which is the "key" column.
   In this scenario, all we need to get is the "COST" for column "C", and the "key" column is "CC" for column "B" in the workbook.
6. Now we can re-run the report, but this time clicking the "Update XLS" option and selecting the TSS Excel file to be updated.
7. Here is our "column Mapping" dialog with our selections input:
   NOTE: Make sure the TSS workbook is closed before trying to update
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
8. Click OK and the report will generate and update the cells in our TSS Workbook.
   I got this error report:
   
   
   It is not significant as all it is warning about are blank cells in the "key" column of the Workbook.
9. Open the TSS workbook and view the outcome, making sure that the cells have been filled in.
   
   
10. Last of all, recalculate the sheet, and make sure the TENDER SUM is correct:
    
    
11. Done.