At the recent User Group Meeting I taught a class on using the internal spreadsheet. I had the following suggestions for creating custom calculations and importing in existing Excel Spreadsheets into HeavyBid’s internal spreadsheet. Here are my notes from the class:

Creating Custom Internal SG Spreadsheets – Best Practices

  1. Don’t merge cells as this makes for a wide spreadsheet that does not format well in the notes.
  2. Do trial runs and test spreadsheets before making live.
  3. What is the goal of the spreadsheet? Decide what fields will be linked in the initial design stage.
  4. Don’t go overboard on the formatting. If in doubt format in the SG version (not Excel).
  5. Regularly review the existing spreadsheets and improve them.
  6. Consider obtaining an Excel spreadsheet consultant to add additional functionality to your custom spreadsheets.
  7. Most Excel spreadsheets that you might use now will require considerable modification before importing in. Usually some of the information in the spreadsheet (like labor $) will be in HeavyBid. You should first perform a detailed design session and modify existing excel spreadsheets before importing them in. Don’t be tempted to import in an existing excel spreadsheet that produces costs without modifications.
  8. If you know Excel well then do all the design in Excel and import into SG.
  9. Do trail imports and test.
  10. Utilize tabs in SG to eliminate multiple spreadsheets.
  11. If spreadsheet is complex and grows horizontally consider a summary page for final calculations results that is narrow enough that it will not get truncated in the notes. ALWAYS THINK OF WHAT IS GOING TO BE BROUGHT INTO THE NOTES
  12. Develop a way to organize your spreadsheets so they are grouped. Rename in File Explorer.
  13. Lock calculation fields especially if spreadsheet is going to be used by others.
  14. Utilize fields that will populate from HeavyBid fields. Fields are self-explanatory. Here are the fields that are the most useful. When you use any of these in a spreadsheet you will get an error but ignore it as it will bring in the value in the SG spreadsheet.

=ACTVQTY       Activity Quantity
=ACTVUNIT    Activity Unit of Measure
=BIDQTY   Bid Quantity
=TOQTY    Takeoff Quantity
=BIDUNIT    Bid Unit of Measure
=CREWHRS    Crew Hours
=PRODRATE   Productivity Rate