Spreadsheets relative cell references

Use Apply Names (Figure 3-10) if you have created a spreadsheet with formulas using cell references and now want to replace the cell references with names. First, use Define Name or Create Names to assign names to the references. Then choose Apply Names. The names that you have assigned will be shown in the list box. The Ignore Relative/Absolute box should usually be checked. Select a name from the list and press OK. All cells containing the reference will be replaced by the name. 

The formula = A 8 B5 refers to cells A8 and B5 in different manners. A 8 is an absolute reference to the contents of cell A8. No matter where cell A 8 is called from in the spreadsheet, the computer goes to cell A8 to look for a number. "B5 is a relative reference in the formula in cell C5. When called from cell C5, the computer goes to cell B5 to find a number. When called from cell C6, the computer goes to cell B6 to look for a number. If called from cell Cl9, the computer would look in cell B19. This is why the cell written without dollar signs is called a relative reference. If you want the computer to always look only in cell B5, then you should write B 5 ... 

As an example of a weak acid problem that can readily be solved by circular reference and iteration, consider the calculation of the pH of a 0.1000 M solution of sodium bisulfate, NaHS04. The bisulfate ion is a relatively strong weak acid with Ka = 1.2 X 10 . (Roughly speaking, weak acids with values greater than 10 will require the quadratic or successive approximations approach.) The spreadsheet fragment in Figure 10-13 illustrates a case of a cell that contains a circular reference. Cell C5 contains the formula =SQRT( C 3 (A5-C5)). 

C2 will use the contents of cell B2. Cell C6 will use the contents of cell B6. In general, references to constants in column A will be absolute (with dollar signs). References to numbers in the remainder of a spreadsheet will usually be relative (without dollar signs). 

---