OST & Spreadsheets

1. OST & Spreadsheets Kevin R. Miller, Ph.D. Brigham Young University kmiller@byu.edu

2. Methods of Exporting • Any Spreadsheet • Copy/Paste • Exporting Data and Layout • Exporting Raw Data (CSV) • Link to Spreadsheets • Excel Only Option • Cell Linking • Name Linking

3. Any Spreadsheet Methods • This is for transferring data to Excel, OpenOffice, Lotus, Quattro Pro, Etc. • The data is not linked with OST. • The data is not automatically updated in either the spreadsheet or OST if changes are made. • To update the spreadsheet data, you must copy and paste or re-export the data again.

4. Any Spreadsheet Methods Cont. • Copy/Paste • Grouping and folders are transferred • Colors are not transferred to the spreadsheet • Basically data only • Exporting Raw Data (CSV) • Basically same as above • Exporting Data and Layout • Same as above except the formatting is nicer and the condition colors are transferred.

5. Copy Paste • From OST Takeoff Tab • Group the data. • Expand or collapse the folders • Sort by pressing on column headers • Drag a Box on the screen to select the conditions to be copied • Right click and select Copy or press Ctrl C • Open the Spreadsheet and Paste • The conditions are now pasted into the spreadsheet

6. Exporting Raw Data • Group the data. • Expand or collapse the folders • Sort by pressing on column headers • Press Print Preview • Select the Export Report Button • The format MS Excel 97- 2000 (Data Only) • If Outlook is not your mail client an error may appear, ignore the error and click OK. • An Excel Format Option window appears, click OK • Tell OST where to save the file and press OK. • Open the file with your spreadsheet application.

7. Exporting – Data & Layout • From OST Takeoff Tab • Group the data. • Expand or collapse the folders • Sort by pressing on column headers • Press Print Preview • Select the Export Report Button • The format MS Excel 97- 2000 • If Outlook is not your mail client an error may appear, just click ok. • An Excel Format Option window appears, click OK • Tell OST where to save the file and press OK. • Open the file with your spreadsheet application.

8. Any Spreadsheet Method • Remember the data is not update if anything changes in OST. The data must be re-export/copied to the spreadsheet.

9. Linking to Spreadsheets • Transfers data from OST to Excel • The transfer is not automatic, it must be SENT. • Updates do not automatically occur. • Works best if OST templates and Excel templates are used.

10. Linking to Spreadsheet Methods • Cell Linking • Inserting or deleting rows or columns in the spreadsheet presents problems • Name Linking • Inserting or deleting rows in the spreadsheet works. • Copying formulas in Excel presents problems. • Using a spreadsheet template minimizes the formula copy problem.

11. Cell Linking Setup • In OST open the Cover Sheet • Change the Price Using field to Microsoft Excel (cell). • In the worksheet field, browse to the template Excel files. • Close the Coversheet window.

12. Cell Linking Procedure • From the Image Tab • Select the condition to be linked to Excel • Change the Properties for the condition. • To the right of the Export Cell, press the spreadsheet icon. • Excel is starts and opens the spreadsheet that was specified on the coversheet. • Select the correct spreadsheet tab, • Type a description in the appropriate cell. • Double click in the cell for the desired link. • You are now returned OST.

13. Cell Linking • In Excel nothing has changed yet. • In OST from the Bid pulldown menu, select Refresh Worksheet. • The Quantities are now sent to Excel. • If you make changes in OST, you must Refresh Worksheet again.

14. Cell Linking WARNING • You must not insert or delete columns or rows in the spreadsheet or the references most likely will be incorrect. • If additional items need to be sent to the spreadsheet, add them at the bottom of the spreadsheet. • If an item is no longer needed in the spreadsheet clear the row. (Do not delete the row or column)

15. Named Linking Setup • Open the Cover Sheet • Change the Price Using field to Microsoft Excel (name). • In the worksheet field, browse to the template Excel files. • Close the Coversheet window.

16. Name Linking Procedure • Same as Cell procedure

17. Name Linking • In Excel nothing has changed yet. • In OST from the Bid pulldown menu, select Refresh Worksheet. • The Quantities are now sent to Excel. • If you make changes in OST, you must Refresh Worksheet again.

18. Name Linking • The linking for the cell vs name process is essentially identical. • The problem is, in Excel, when the formulas are copied, the copied formula reference the named cell rather than the relative cell. • English translation. • All the copied cells refer to the first quantity that was linked rather than the quantities on the row.

19. Work around • In the spreadsheet, let the first row of the spreadsheet be a formula row that can be copied. • In otherwords, the second row is where the first quantity should be placed.

20. Why Name Linking • Once you understand how it works, named cells allow you to: • Insert rows or columns • Delete rows or columns • Cut and paste the referenced cell to a new location on the worksheet.

21. Other Considerations • Sanity Checks. • For the quick, estimate does it make sense to setup the linking?

22. Thank Youkmiller@byu.edu