Worksheet Names

Note that the EXCEL engine specification is optional, because SAS would read the .xls extension in the physical filename and assume it indicates a Microsoft Excel file. Also note that the xlsfile libref refers to the entire Excel workbook. In the subsequent PROC PRINT, the normal ranges must be specified so SAS will know which Excel worksheet to read. The data set/worksheet name in the PROC PRINT looks odd because of the existence of a special character, which is normally not allowed as part of a data set name. 

OBTAIN AVAILABLE WORKSHEET NAMES FROM EXCEL FILE proc sql ... 

Alternatively, you can have Excel supply the worksheet name and cell reference. In the formula bar, type Activate the worksheet by clicking on it or by selecting it from the Window menu. The external reference to the worksheet will appear in the formula bar. Select the desired cell in the worksheet. The absolute reference to the cell will appear in the formula bar. 

JjE Alternatively, you can have Excel supply the worksheet name and cell ... 

As shown in Figure 4.B.2, the Aspen Plus simulation file is opened first. In VBA, Dim statement is used for declaration of variables, whereas Set statement is used for creating new objects. Then, values of feed flow rate, feed heater outlet temperature, reflux ratio for column 1 and feed stage for column 2 and number of stages for column 3 are transferred from cells C3 to C7 in Excel worksheet named DV to Aspen Plus , to Aspen Plus... 

Aspen Plus simulation is now activated from VBA. After the convergence of process simulation, values of energy input for feed heater, columns 2 and 3 reboiler duty, and flow rates of all three product streams are transferred from the simulator to cells C3 to C8 in Excel worksheet named Data from Aspen Plus . VBA code for these variables and/or data transfer is given in Figure 4.B.2... 

The other Mathcad worksheet which is also shown in Fig. 6.31 contains operators of source data (a =1 b =2) and the result (c = 3). The worksheet named sheet.mcd carries out the calculations contained in the referenced worksheet (which is coimected to first worksheet by the command Insert I Reference). As a rule, the referenced worksheets contain constants and functions covering a branch of applied sciences or having some general engineering significance. Reference files allows, for instance, a dimensional set of user-defined functions in the form of DLL that have been turned into built-in, i.e. listed in the Insert Function dialog box (see site http //www.wsp.ru). 

After you click Next, a Select Table window pops up, allowing you to enter a name for your Excel worksheet. We call the worksheet DM in this example, as follows ... 

To enter the first label, move the pointer to cell C6, type Variable and press RETURN. In the same way enter the labels Nominal and Extreme in D6 and E6. The worksheet identifies all these names as labels by the fact that they begin with a letter. Labels beginning with a number can also be entered and are then identified with a sign. Then one can enter the variable names. 

Another of the most important features of LOTUS is now used. In 117 one puts in the equation for DaoW, in M17 the one for DDr, etc. The result will be that as soon as the experimental values needed to compute the equations have been filled in column W, the result of the computations will appear in row 17. Let us now see how this is done in practice. There are in fact two ways to do it, namely by using the pointer or by typing. The latter has our preference. One moves first the pointer to cell 117. Then one types the pertinent equation using the cell addresses, where the information needed is stored. For instance, the first element in the equation for DA is Yi. This is found in W8 and therefore in the worksheet equation W8 replaces Yi. The equation in 117 should be ... 

Select / Range Name Create, enter A as the name, and enter 1E1 as the range. It is a good idea to put a label in the worksheet to help remember that this macro was named A. One can put macro names in the cell to the immediate left of the macro. Move to ID1 and enter A. 

The values marked in bold have to be changed according to the current data range. If data has been pasted into table 1 replacing the test data, the name of the worksheet needs no further modification. If the. csv file has been opened directly, the name of the current worksheet has to be entered in the macro. 

Each spreadsheet file can contain several worksheets, each of which either contain data or graphs and which can also be named. The default is Sheet , Sheet.2, etc., but it is possible to call die sheets by almost any name, such as Data, Results, Spectra, and it is also possible to contain spaces, e.g. Sample 1A. A cell specific to one sheet has die address of die sheet included, separated by a , so die cell Data E3 is die address of die diird row and fifth column on a worksheet Data. This address can be used in any worksheet, and so allows results of calculations to be placed in different worksheets. If die name contains a space, it is cited within quotation marks, for example Spectrum l B12. 

The name of a matrix is common to an entire spreadsheet, rather titan any individual worksheet. This means that it is possible store a matrix called Data on one worksheet and then perform operations on it in a separate worksheet. 

Changes (a) - (d) are then made. Finally, the worksheet is saved in the Chapter 16 file with an appropriate name, e.g. Table N. 1. 

Using a DFMEA worksheet (Exhibit 40.1), complete the product/service name, the name of the team leader who prepared the DFMEA, and when it was initiated. 

For the MHI study, three appraisers were given 20 sample resumes as part of a larger group. A week later the 20 samples, with only the applicant names changed, were mixed into another group. The appraisal results were recorded in a standard Attribute MSA worksheet (Exhibit 47.1). 

You can use Save As... to create a backup copy of a worksheet by giving the copy a different name. 

This chapter shows you how to use Excel s wide range of worksheet functions to construct sophisticated worksheet formulas. In addition, you ll learn how to use named references in formulas, which makes constructing complicated formulas an easier and more error-free task. You ll also learn techniques of formula editing and worksheet troubleshooting. 

Links between worksheets are established by means of external references. An external reference is simply a reference that includes the filename of the source worksheet, separated from the reference or name by an exclamation point, e.g., =SheetName CellReference if the link is between sheets in the same workbook or [WorkbookName]SheetName CellReference if the link is between sheets in different workbooks. 

If you edit a source worksheet, the cell references in the dependent worksheet will be updated, provided the dependent worksheet is open. The same is true if you change the name of a source document or move it to a different directory. If you rename or move a source document while the dependent document is closed, references will not be updated and you ll get a "Can t find document" message, and will have to re-establish the links between the documents. 

The first character of a name must be a letter. Subsequent characters can be letters or numbers or the period or underline character. Spaces are not allowed (the space character is the intersection operator) Excel will substitute an underline character for a space in any name that it proposes based on text in worksheet cells. 

Instead of a reference, you can type a numeric value or a formula in the Refers To box. You can use named formulas to simplify long worksheet formulas by assigning names to parts of the formula. See "Returning an Array of Unique Entries in a List" in Chapter 4 or "A Drop-down List Box on a Worksheet" in Chapter 8 for examples of this technique. 

You ll find Create Names very useful if you have worksheets with constants or other values arranged in a table format, as in Figure 3-3. This command allows... 

Names can be enclosed in parentheses for clarity in the worksheet, as in Figure 3-5 the parentheses will be ignored in creating the names. Here the cells F4 G9 are selected and Excel proposes to use names in the Right Column. Equal signs and colons are also ignored, as in Figure 3-6. 

The Paste Name command allows you to select a variable name from the list of names in the worksheet and Paste it into a cell or formula. There doesn t seem to be any advantage to this command over simply typing in the name, or selecting the named cell or range. 

If you want to create a name that is a local or worksheet-level name, that is, one that is available only in a particular sheet, you can do so as described in the following box. 

---