Excel naming worksheets

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 ... 

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 ... 

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. 

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. 

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. 

If you type the function name in a worksheet cell and then press CONTROL+SHIFT+A, Excel will paste dummy arguments, called placeholder arguments, and add the closing parenthesis. For example, after entering =LINEST, press CONTROL+SHIFT+A. The function will be completed and will appear as follows ... 

NAME is displayed when you use a name that Excel doesn t recognize. Most often this occurs when you misspell a name or function, or when you enter a name without having defined it. It will also happen when you enter a text argument in a formula and forget to enclose the text in quotes, or when you use a worksheet function in a formula and forget to include the parentheses and/or arguments. 

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. 

A convenient way to edit existing records or enter new data in a database is by using Excel s Data Form. The database range must already have been defined, either by using Set Database or by selecting a cell within the block of cells that comprise the database. Choose Form... from the Data menu. The Data Form dialog box will appear, with the name of the worksheet in the title bar, as in Figure 6-9. 

Now choose Advanced Filter... from the Filter... submenu of the Data menu. Excel will display the Advanced Filter dialog box (Figure 6-10). Press the button for Copy To Another Location and, if you have assigned the name Extract, Excel will automatically enter the reference to the Extract range otherwise, enter a reference in the input box. When you press OK, the records that meet the criteria will be copied to the Extract range (Figure 6-15). (Remember that all cells from the bottom of the Extract range to the bottom of the worksheet will be cleared.)... 

Excel Tip. Be sure to select a range of empty cells as the Extract range. If you select only the field names as the Extract range, the Extract command will clear all cells below the Extract field names to the bottom of the worksheet, regardless of whether values are extracted into them, erasing any previous information. 

Function procedures augment Excel s library of built-in functions. A function macro is used in a worksheet in the same way as, for example, the SORT function. It is entered in a single cell of a worksheet, performs a calculation and returns a single result (or an array result) to the cell in which it is located. For example, a custom function macro named ALPHA can be used to calculate aj, the fraction of an acid-base species in one of its protonated forms HjX at a particular pH. The function takes three arguments the pH of the solution, the range of pKa values of the weak acid and the coefficient j. This function is useful in constructing distribution diagrams, titration curves, and so on. 

In an Excel worksheet, you have to use Define Name or Create Name to assign a name to a variable, but variable names in VBA are automatically assigned as you type the VBA code in a module. There are just a few rules for naming variables or arguments ... 

The CD-ROM that accompanies this book contains most of the worksheets that are discussed in the book. The files are in Excel 97 format, so that they can be opened using either Excel 97/98 or Excel 2000/2001. The document names have. xls file extensions, so that they are compatible with Excel for Windows. Macintosh users can delete these file extensions if they wish. 

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

Launch Microsoft Excel. Then, open the Microarray Analysis Workbook. On opening the workbook, you will notice that it contains four worksheets. The names of these sheets appear on tabs at the bottom of the workbook window. The Ab, Array, and Ab List worksheets contain array-specific information such as the names and coordinates of antibodies and the Locus Link and SWISS-PROT accession numbers of the corresponding protein targets. 

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... 

We chose the second solution and implemented it in the form of annotation tables. From the user s perspective, an annotation table is simply a table of rows and columns that reflects the data stored in an associated Excel worksheet or a file. An annotation table normally contains a registry number column that is used to link the table to the rest of the database and a set of additional columns in the source file that define the data of interest. To create an annotation table, the user simply supplies the name of the file, a project that it should be hnked to, and a data security setting to Mobius. The file is then imported into a set of Oracle tables that hold annotation table data where it is then available for use on par with the other available data sources. Mobius can check for changes to the source file and update the table in Oracle whenever the source file changes if desired. Additionally, annotation table data can be... 

The object Worksheets (ActiveSheet.Name) takes on the identity of the active worksheet (the one that is currently showing when the Excel window is active). 

---