Advanced techniques for names IT Service

Ir a contenido Ir a Estudios, Gobernanza y organización
Logo UA
Realizar búsqueda
Menú
Services
Logo IT Service   IT Service
IT Service

Advanced techniques for names

Here are some of the techniques that exist to name cells, additional to those seen in the previous academic year.

  1. Sheet-level names
  2. Use of multiple sheets
  3. Naming constants
  4. Name formulas
  5. Apply names to existing references

 

1. Sheet-level names

By default, names that are created in any part of the book of job are names at the book of job. If we want to use the same name in several sheets of the book (as Total_Departamento, for example), we can do this by defining the names at the spreadsheet.

To this end, we must activate the worksheet where you want to define the name, on the formulas tab, in the defined names group, click Define name. In the dialog box, select the field (book or leaves). For example, to define the name Total_Departamento in Hoja2, you should select the Hoja2 sheet and enter in the dialog box define name, Total_Departamento

Also you can create a name at the leaf level using the names dialog box to the left of the formula bar. After you select the cell or range, we will click in this box and we introduce the name preceded by the name of the road and a sign of exclamation. Finally, press ENTER.

If you use a name at the level of leaf within a formula that is on the same sheet, it is not necessary to include the name of the sheet to make reference to the cell or range. If the formula is written in a different sheet, it is necessary to type the entire name.

Only at the level of the active sheet sheet names appear in the names box. Similarly, only at the level of the active sheet sheet names appear in the list when open dialog boxes Paste name or define name.

 

2. Use of name in multiple sheets

To create a name for multiple sheets, use dialog set name (insert name, define) and introduce, by hand, the reference to the cells in the paragraph refers to. The format for a reference to multiple sheets is as follows:

PrimeraHoja:ÚltimaHoja!ReferenciaRango

The following figure shows a name of many leaves, which it is defined for A1:C12 and extends through the leaves entities, Hoja2 and page3.

Tec1

After defining the name (although we must take into account that this name will not appear in the names box) you can use in formulas.

 

3 Naming constants

In addition to named cells and ranges, also you can give name to constant values that do not appear in any cell. See it with an example: in a spreadsheet need to use a percentage which we will call in several formulas imposed on sale. With what we already know about Excel, we can think of include this value in a cell and use the reference to it in formulas. Even, to make it more easy to appoint this reference as Impuesto_Ventas.

But Excel offers another possibility: on the formulas tab, in the defined names group, click Define name (or press Control-F3). In the define name dialog box, enter the name (in this case Impuesto_Ventas) in the section names in the book. In refers to delete content and replace it with the 0.075 value (see figure below). Not need to be introduced before the sign =. Then, press OK. From this moment, when write Impuesto_Ventas, Excel will take the value 0.075.

Tec2

The constant, as well as numerical, can also be a text.

The names of constants do not appear in the names box or in the table of dialogue go to only appear in the dialog box Paste name.

 

4 Appoint formulas

Similarly to appoint constants, Excel allows you to appoint formulas that do not appear in any cell. When we write the name of one of these formulas, Excel will perform calculations and will show us the result.

References to the cells can be relative or absolute. The following figure shows an example of a name of formula with absolute references to cells. The Porcentaje_mensual name refers to the formula:

= Page3!$ B$ 1/12

Tec3

The following figure shows an example of a formula with references for cell name. In this case, the force name refers to the formula:

= Entities!A1 ^ entities!B1

Tec4

 

5 Applying names to existing references

If we put name to cells having written a formula that makes reference to them, Excel does not change automatically formulas to show them with new names.

To achieve this, we select on the formulas tab, in the defined names group, applying names. The dialog box then appears in dialog apply names such as what we see in the figure below. Then you just have to select the names and click OK. To do so, Excel replaces references to ranges with the names of the selected cells

Tec5