Transcript Exporting To Spreadsheet
OST & Spreadsheets
Kevin R. Miller, Ph.D.
Brigham Young University [email protected]
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
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.
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.
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
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.
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.
Any Spreadsheet Method
• Remember the data is not update if
anything changes in OST. The data must be re-export/copied to the spreadsheet.
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.
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.
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.
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.
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.
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)
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.
Name Linking Procedure
• Same as Cell procedure
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.
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.
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.
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.
Other Considerations
• Sanity Checks. • For the quick, estimate does it make
sense to setup the linking?