Excel Expert Session 4: Using Names and the Formula Auditing Tools
- The #NULL! error occurred because Excel is confused by the use of the intersection (space) operator. *It usually simply means that you've incorrectly typed one of a function's arguments.* 1. Examine the formula in cell B16: =B4+B5 B6 - The intersection operator was covered in: Lesson 4-5: Use intersection range names and the INDIRECT function - Excel has interpreted the missing operator as an attempt to reference the intersection of cells B5 and B6. It is, of course, impossible to have an intersection between two single cell references 2. Correct the formula in cell B16 by adding the missing addition operator: =B4+B5+B6 3. Press the <Enter> key.
Lesson 4-10: Understand the #VALUE!, #REF! and #NULL! error values (file Errors-2): *Diagnose and solve the problem causing the #NULL! error in cell B16.*
- The #REF! error means that a formula refers to a cell that isn't valid. This happens when you reference a cell from a formula and then *delete the row or column that used to contain the referenced cell* 1. Examine the formula in cell *B11: =B10/#REF!* Because this cell needs to convert the dollar sales figure in cell B10 to Great Britain Pounds (GBP), the missing reference is the USD/GBP exchange rate. 2. Replace the #REF! part of the formula with 1.62 (the USD/GBP exchange rate when this lesson was written). 3. Press the <Enter> key
Lesson 4-10: Understand the #VALUE!, #REF! and #NULL! error values (file Errors-2): *Diagnose and solve the problem causing the #REF! error in cell B11.*
- The #VALUE! error means that a function contains an invalid argument 1. Examine the formula in cell F8: =IF("B8>C8", "Yes", "No") - The first argument of an IF function demands a logical expression that evaluates TRUE or FALSE. - There's nothing wrong with the logical expression: B8>C8. The problem is caused by the quotation marks that cause Excel to interpret it as text 2. Correct the formula in cell F8 by removing the quotation marks from the expression B8>C8 (the IF function's first argument).
Lesson 4-10: Understand the #VALUE!, #REF! and #NULL! error values (file Errors-2): *Diagnose and solve the problem causing the #VALUE! error in cell F8.*
1. Click: File->Options->Formulas. - Note that Excel monitors for eight different error conditions by default. There's a ninth that is switched off by default but can be enabled if required:
Lesson 4-11: Understand background error checking and error checking rules (FILE-DAILY PROFIT REPORT 1) : Understand error checking rules
1. Click on cell D6 and hover the mouse cursor over the Smart Tag. - This time Excel has found a real error. It has noticed that the formula in cell D6 is inconsistent with the other formulas in column D. The formula is adding costs to sales when it should be subtracting them. 2. Click the down arrow on the Smart Tag and click Copy Formula From Above from the shortcut menu. - The error is corrected.
Lesson 4-11: Understand background error checking and error checking rules (FILE-DAILY PROFIT REPORT 1): Correct the error in cell D6.
1. Click on cell A5. Notice that an Error Smart Tag has appeared to the right of the cell. 2. Hover the mouse cursor over the Smart Tag - A tip appears showing the rule that Excel believes has been violated: 3. Click the down arrow on the Smart Tag - Excel is worried because the date (unlike the other dates in the column) has been entered as a text value with two digits. (A residual effect of Y2K fears) - A date can be entered in this way by preceding the date by an apostrophe to indicate that the entry is a textual value: '19-Sep-16 - Notice that two of the options in the Smart Tag list offer to convert the date to 2016 or to 1916. 4. Click Ignore Error.
Lesson 4-11: Understand background error checking and error checking rules (FILE-DAILY PROFIT REPORT 1): Remove the error warning from cell A5.
Sometimes those little green triangles can be annoying. Excel often picks up "errors" that are not really errors at all. Some Excel users would prefer to switch off background error checking and instead run a manual error check when the worksheet is complete. In this lesson, you'll switch off Excel's background error checking andthen manually scan a worksheet for errors.
Lesson 4-12: Manually check a worksheet for errors: *What is the objective of the lesson?*
1. Click: Formulas->Formula Auditing->Error Checking. - Cell D6 is selected (the first cell in the worksheet containing a suspected error) and the Error Checking dialog appears: - The dialog indicates that Excel has detected an inconsistent formula. This means that the formula in cell D6 is not consistent with the other formulas in column D 2. Click: Formulas->Formula Auditing->Show Formulas. The formulas behind the cells are now displayed: - For some reason an extra 1,000 has been deducted from the variance for March 2016. 3. Click: Formulas->Formula Auditing->Show Formulas. to turn the feature off., and select *resume* in the dialog 4. Click Copy Formula from Above to correct the error.
Lesson 4-12: Manually check a worksheet for errors: Manually check the worksheet for errors (Operating Expenses-1): *Correct the error in Cell D6*
1. Click: File->Options->Formulas. 2. Check the Enable background error checking check box. 3. Click the OK button. - Notice that there are no longer any green triangles as you've corrected all of the errors
Lesson 4-12: Manually check a worksheet for errors: Manually check the worksheet for errors (Operating Expenses-1): *Re-enable background error checking*
1. Click into cell D7. - Notice that it isn't possible to immediately see which cells are referenced by the Profit range name. 2. Click: Formulas->Formula Auditing->Trace Precedents. The cells referenced by the Profit range name are now apparent. 3. Click: Formulas->Formula Auditing->Trace Precedents again. This time the next level of precedents is shown Click: Formulas->Formula Auditing->Trace Precedents again. - The icons pointing to cells B4:C6 indicate that their values come from different worksheets in this (or even another) workbook.
Lesson 4-13: Audit a formula by tracing precedents (Profit Analysis-1): Audit the formula in cell D7 of the First Quarter Summary worksheet using Trace Precedents.
4. Double-click the dotted line joining cell B4 and the icon - The Go To dialog appears showing the source of the value in cell B4. 5. Select the item shown in the Go To window and then click the OK button - You are taken to the cell in the January worksheet that provides the value shown in cell B4 on the First Quarter Summary worksheet. 6. Select the First Quarter Summary worksheet. 7. Click: Formulas->Formula Auditing->Remove Arrows. The precedent arrows are removed from the First Quarter Summary worksheet.
Lesson 4-13: Audit a formula by tracing precedents (Profit Analysis-1): Finish auditing the formula
1. Click cell D6 to make it the active cell. 2. Click: Formulas->Formula Auditing->Trace Dependents. The direct dependents are shown for cell D6. These cells have formulas that directly reference cell D6. 3. Continue using the Trace Dependents feature to analyze the workbook.
Lesson 4-14: Audit a formula by tracing dependents : *Audit the formula in cell D6 using Trace Dependents*
1. Click on the February worksheet tab. 2. Change Johnny Caine's sales (in cell D4) to 20,000 and press the *<Enter>* key. - Note that the values in the Watch Window have changed to reflect the new value. 3. Close the Watch Window.
Lesson 4-15: Use the Watch Window to monitor cell values (file-Profit anaysis-1): *Change some values on the February worksheet while monitoring the values defined in the Watch Window.*
1. Click cell B7 to make it the active cell. 2. Click: Formulas->Formula Auditing->Watch Window. - The Watch Window appears. 3. Click the Add Watch... button. The Add Watch dialog appears: 4. Click the Add button. - The watch cell details appear in the Watch Window. 5. Add the C7, and D7 cells to the window in the same way - You can Dock the window to top of the screen with the 4- headed arrow - You can convert the window to a floating dialog by dragging it onto the worksheet grid.
Lesson 4-15: Use the Watch Window to monitor cell values (file-Profit anaysis-1): *Set up a Watch Window to monitor the values in cells B7, C7 and D7.*
1. Click: File->Options->Quick Access Toolbar. The Customize the Quick Access Toolbar pane appears. 2. Select Commands Not in the Ribbon from the Choose commands from drop-down list. 3. Click the Speak Cells command in the list of commands: 4. Click the Add button 5. Click the Add button four more times to add all Speak Cells commands to the Quick Access Toolbar. 6. Click the OK button.
Lesson 4-16: Use Speak Cells to eliminate data entry errors (file-Profit Analysis-1): *Add custom buttons to the Quick Access Toolbar for all of the Speak Cells commands.*
3 Type Bonus into cell G3 on the January worksheet. 4 If necessary, apply the Heading 3 cell style to cell G3. 5 Click in cell G4. 6 Click the Speak Cells on Enter button that you added to the Quick Access Toolbar. - You should hear the words "Cells will now be spoken on enter". If you don't hear this, your speakers are either muted or not working. 7 Add the following values to column G without looking at the Excel screen. - Notice that Excel speaks the number back to you every time that you press the <Enter> key. This means that you can be sure that you added the correct value without having to continually look from paper to screen.
Lesson 4-16: Use Speak Cells to eliminate data entry errors (file-Profit Analysis-1): *Demonstrate the use of the various Speak Cells features*
1. Click in cell C5. 2. Click: Formulas->Defined Names->Define Name. - The New Name dialog appears. 3. Type the name MeterToFoot into the Name box. Leave the scope as Workbook (see sidebar for an explanation of scope). 4. Click the OK button
Lesson 4-2: Manually create single cell range names and named constants (Distance-1): *Manually create a range name for the Meter to Foot conversion factor using the Define Name dialog.*
1. Click in cell D4 on the Distances worksheet. 2. Type: =C4* - You now need to insert the conversion factor to convert Kilometers to Miles. 3. Click: Formulas->Defined Names->Use in Formula. - A drop-down list appears showing all currently defined range names: 4. Click KilometerToMile, and hit enter.
Lesson 4-2: Manually create single cell range names and named constants (Distance-1): *Use formulas containing range names to calculate distances in miles on the Distances worksheet*
1. Click in cell E4. 2. Type =C4* to start the formula. 3. Click: Formulas->Defined Names->Use in Formula to see the list of defined names. 4. Click KilometerToNauticalMile from the drop down list. 5. Hit <Enter> 6. Autofill down the list
Lesson 4-2: Manually create single cell range names and named constants (Distance-1): *Use the KilometerToNauticalMile named constant to add a formula to cell E4 that will calculate the distance from London to Paris in nautical miles*
1. Show formulas instead of values within the worksheet. - Click: Formulas->Formula Auditing->Show Formulas 2.Select cells A3:B8. 3. Click: Formulas->Defined Names->Create from Selection. 4. Click OK. - Due to a bug in Excel 2016 you may have to correct the formula for Net Profit manually to: = Gross_Profit - Commission - Tax
Lesson 4-3: Use range names to make formulas more readable (Sales and Profit-1): *Create automatic range names for all of the values in cells B3:B8.*
1. Click: Formulas->Defined Names->Define Name (Drop-down arrow)->Apply Names... The Apply Names dialog appears. 2. Click the OK button
Lesson 4-3: Use range names to make formulas more readable (Sales and Profit-1):Apply the new range names to the existing formulas.
1. Type Average Sales into cell A20. 2. Type Average Bonus into cell A21. 3. Click in cell B20 and type: =AVERAGE(S - Notice that Formula AutoComplete shows both functions and defined names beginning with S: 4. Press the <Tab> key to insert the Sales range name, and hit <Enter>. 5. Use the same technique to calculate average bonus in cell B21. 6. Widen the cell, apply the comma style to both values
Lesson 4-4: Automatically create range names in two dimensions (Earnings Summary-2): *Add formulas to cells B20 & B21 to calculate average sales and average bonus respectively.*
1. Add a new worksheet (you can leave it at the default name of Sheet2). 2. Copy cells A20:B21 from the Sheet1 worksheet and then paste into cells A1:B2 on the Sheet2 worksheet. - You can now appreciate one of the great advantages of range names. The formula on Sheet2 is exactly the same as that on Sheet1 and still works correctly. - Without the use of range names, it wouldn't have been possible to copy and paste the formulas between worksheets. You would have had to re-write the formula as: =AVERAGE(Sheet1!B9:B17)
Lesson 4-4: Automatically create range names in two dimensions (Earnings Summary-2): *Copy Cells A20:B21 to a new worksheet.
1. Click: Formulas->Defined Names->Name Manager. - The Name Manager is fantastically useful when working with names. It lists all currently defined names and allows you to edit them. - Note the Filter drop-down that allows you to show a subset of names. This is useful when working with workbooks that contain large numbers of names. - You can see that you have created a range name for each employee (named in column A) and for each of the columns (named in row 8).
Lesson 4-4: Automatically create range names in two dimensions (Earnings Summary-2): *View the range names created using the Name Manager.*
- From the previous step it would seem that we could use the intersection operator to simply write the formula =B30 B31, unfortunately this will not work. You must use the INDIRECT function to accomplish this. 1. Type the following formula into cell B32: *=INDIRECT(B30) INDIRECT(B31)* - Note that there is a space between =INDIRECT(B30) and INDIRECT(B31). 2. Press the <Enter> key
Lesson 4-5: Use intersection range names and the INDIRECT function (Distance Chart 1): *Use the INDIRECT function and the intersection operator (space) to show the correct distance in cell B32 based upon the values in cells B30 and B31.*
Use the intersection operator to create a formula in cell B32 that will show the distance from Birmingham to Oxford. 1. Click in cell B32. 2. Type the formula: *=Birmingham_ Oxford* - Note that there is a space between the words Birmingham_ and Oxford. 3. Press the *<Enter>* key.
Lesson 4-5: Use intersection range names and the INDIRECT function (Distance Chart 1): *Use the intersection operator to create a formula in cell B32 that will show the distance from Birmingham to Oxford.*
1. Click: Formulas->Defined Names->Name Manager. The Name Manager dialog appears. - It is now clear why trailing underscores were used for the labels in column A. Without them, there would have been a naming conflict as each place name appears for both a row and a column 2. Click the close button
Lesson 4-5: Use intersection range names and the INDIRECT function (Distance Chart 1): *View the range names created using the Name Manager*
1. The demonstrates two methods of accomplishing the same task: Creating a formula that will display the distance between two cities, referenced from a distance chart. The two methods are: a. *The intersection operator alone* - With help from a validation list to avoid typos. b. *The INDIRECT function + intersection operator* - This method allows you to circumvent using cell range names altogether and simply refer to base cell names (letter and number)
Lesson 4-5: Use intersection range names and the INDIRECT function (Distance Chart 1): What is the purpose of this lesson?
1. Click anywhere in the range A3:A7 on the Validation worksheet. 2. Click: *Insert->Tables->Table.* 3. Click the OK button. 4. Do exactly the same thing for the Departments range
Lesson 4-7: Create table-based dynamic range names (Human Resources-2): *Convert the ranges A3:A7 and C3:C7 on the Validations worksheet into tables.*
Unfortunately, Excel doesn't accept a table name for a list validation. Fortunately, it is happy to accept a range name associated with a table. 1. Select all of the values in the Office table (cells A4:A7), - Be careful not to include the header row. 2. Click: Formulas->Defined Names->Define Name. The New Name dialog appears. 3. Type OfficeTable in the Name text box. - This means that the structured reference refers to all of the data in the Office table. In other words, it is a dynamic named range that will shrink and grow with the Office table. 4. Click the OK button. 5. Create a DepartmentTable range name in exactly the same way.
Lesson 4-7: Create table-based dynamic range names (Human Resources-2): *Create range names called OfficeTable and DepartmentTable based upon the two new tables.*
1. Select the *Validations* worksheet. 2. Click: *Formulas->Defined Names->Name Manager.* 3. Select Departments and Offices and then click the Delete button. 4. Click OK to confirm that you want to delete. 5. Click the Close button to close the Name Manager.
Lesson 4-7: Create table-based dynamic range names (Human Resources-2): *Delete the existing range names for Departments and Offices.*
1. Select column B on the Employees worksheet. 2. Click: Data->Data Tools->Data Validation. 3. Delete =Offices from the Source text box. 4. Press the <F3> key to bring up a list of range names. 5. Click: OfficeTable. This is the fastest way to bring up the Paste Name dialog. The slower ribbon method is to click: Formulas->Defined Names->Use In Formula->Paste Names 6. Click the OK button. 7. Click the OK button on the Data Validation dialog. 8. Use the same method to edit the list validation for column C. 9. Be sure to test the validations list by adding and removing a name from both ranges (on the validations worksheet).
Lesson 4-7: Create table-based dynamic range names (Human Resources-2): *Edit the list validation on columns B and C in the Employees worksheet to reference the OfficeTable and DepartmentTable range names.*
1. Click anywhere inside the range A3:A7. 2. Click *Table Tools->Design->Properties->Table Name.* 3. Type Office into the text box. 4. Do exactly the same thing to name the Department table.
Lesson 4-7: Create table-based dynamic range names (Human Resources-2): *Name the two new tables Office and Department.*
1. Excel doesn't except table names as the source for list validations, and ranges are not dynamic- in that they do not expand and shrink as items are added and removed. 2. Therefore in order to not have re-define the range (for the validation list) each time it must be altered, you can: 1. Create a table from the original source range, then: 2. Create a dynamic range name based on the new table. 3. This will allow you to freely add and remove items from the source table, without having to redo the entire validation from scratch.
Lesson 4-7: Create table-based dynamic range names (Human Resources-2): *What is the purpose of this lesson?
1. Select column A on the Cars worksheet. 2. Click: *Data->Data Tools->Data Validation*. 3. Enter the following values into the dialog: Allow: List Data: between Source: =MAKE 4. Click the OK button
Lesson 4-8 (Car Model Range-1) : Create two linked drop-down lists using range names: *Apply a list validation to column A of the Cars worksheet that uses the Make range name as its source.*
1. Select cells A12:C15. 2. Click *Formulas->Defined Names->Create from Selection.* 3. De-select the Left column check box because you only want the range names to use the labels from the Top row. 4. Click the OK button.
Lesson 4-8 (Car Model Range-1): Create two linked drop-down lists using range names: *Create automatic range names for each Model on the Model Range worksheet*
1. Select the Model Range worksheet. 2. Select cells A5:A8. 3. Click: Formulas->Defined Names->Create From Selection. 4. Click the OK button.
Lesson 4-8: Create two linked drop-down lists using range names (Car Model Range-1): *Create an automatic range name for the Make table on the Model Range worksheet.*
1. Select cells A12:C15. 2. Click Formulas->Defined Names->Create from Selection. 3. De-select the Left column check box because you only want the range names to use the labels from the Top row. 4. Click the OK button
Lesson 4-8: Create two linked drop-down lists using range names (Car Model Range-1): *Create automatic range names for each Model on the Model Range worksheet.*
- Previously we have learned to create validation lists that are not directly correlated. - This lesson demonstrates how create validations that are dependent. - This lesson provides a solution to the problem of dynamically providing the correct Model range (of cars) that is dependent on the Make selected.
Lesson 4-8: Create two linked drop-down lists using range names (Car Model Range-1): *What is the objective of this lesson?*
1. Use the drop-down list to select a car make in cell A4 2. Click in cell B4. 3. Click: Data->Data Tools->Data Validation. 4. Enter the following values into the dialog: Allow: List Data: between Source: =INDIRECT(A4) 5. Click the OK button
Lesson 4-8: Create two linked drop-down lists using range names: *Apply a list validation to B4 that will display a list of models based upon the make displayed in cell A4.*i
1. Copy the contents of cell B4. 2. Select all of column B. 3. Right-click inside the selected range and click Paste Special...from the shortcut menu. 4. Select Validation from the Paste Special dialog. 5. Click the OK button. 6. Test the validation - The valid choices for Model now change based upon the Make selected in column A.
Lesson 4-8: Create two linked drop-down lists using range names: *Copy and paste the validation to all of the cells in column B.*
- Dividing by zero results in an infinite number, causing Excel's *"Divide by Zero" error* to be displayed 1. Examine the formula in cell E5: =B5/C5 - C5 has no value entered for the sales target, thus causing the error. 2. Enter a sales target of 3,250 for Febuary. - The problem is solved. - If you didn't know the target for February and wanted to suppress the error message, you could use the IFERROR function as described in: *Lesson 3-24: Use an IFERROR function to suppress error messages*
Lesson 4-9: Understand the #NUM!, #DIV/0! and #NAME? error values (file: Errors -1): *Diagnose and solve the problem causing the #DIV/0! error in cell E5.*
- The #NAME? error means that Excel has encountered a *name it doesn't understand*. The most likely cause is that you've used a range name that doesn't exist. 1. Examine the formula in cell C10: =SUM(Target) 2. Select cells C3:C9. 3. Click: Formulas->Defined Names->Create from Selection. 4. Click the OK button.
Lesson 4-9: Understand the #NUM!, #DIV/0! and #NAME? error values (file: Errors -1): *Diagnose and solve the problem causing the #NAME? error in cell C10.*
- The #NUM! error is usually caused by a formula that returns a number that is *too large*, or *too small* for Excel to handle. - Another possible cause is a non-numeric value entered as an argument for a function that expects a numeric value. 1. Examine the formula in cell D4: =B4^C4 - The error is being caused by Excel calculate B4 exponent C4 which results in a number which is too high for Excel to handle. 2. Correct the formula. As you need to calculate how far sales are above or below target you will need to correct the formula to: =B4-C4 - In addition to removing the error, the #NUM! error in cell D10 also disappears.
Lesson 4-9: Understand the #NUM!, #DIV/0! and #NAME? error values (file: Errors -1): *Diagnose and solve the problem causing the #NUM! error in cell D4.*
1. Click: File->Options->Formulas. 2. Clear the Enable background error checking check box: 3. Click the OK button. - Notice that the green triangles have now vanished, even though the error conditions remain.
Lesson 4-12: Manually check a worksheet for errors (Operating Expenses-1): *Switch off background error checking.*
10 Click the Speak Cells on Enter button that you added to the Quick Access Toolbar. - You should hear the words "Turned off speak on enter". If you don't do this, Excel will continue to speak to you when you remove the toolbar buttons! 11 Remove the Speak Cells buttons from the Quick Access Toolbar. - Right-click on each Speak Cells button in turn and then click Remove from Quick Access Toolbar
Lesson 4-16: Use Speak Cells to eliminate data entry errors (file-Profit Analysis-1): *Turn off the Speak Cells features, then remove them*
1. Click the ExchangeRates worksheet tab. 2. Select the range A3:B5. 3. Click: Formulas->Defined Names->Create from Selection. - The Create Names from Selection dialog appears: - Notice that Excel has correctly guessed that the labels for each exchange rate are in the left column. 4. Click the OK button. 5. Click the drop-down arrow on the right of the Name box to view the range names.
Lesson 4-1: Automatically create single-cell range names (uses file Vintage Wines-1): *Automatically create a range name for each of the exchange rates.*
1. Click the Prices worksheet tab. 2. Click in cell D4. 3. Type =C4/ to begin the formula. 4. Click: Formulas->Defined Names->Use in Formula. A drop-down list appears containing all defined range names 5. Click: GBP_USD to insert the range name, and hit enter. 6. Use the same technique to enter a formula to calculate the Euro and Japanese Yen prices in cells E4 and F4.
Lesson 4-1: Automatically create single-cell range names (uses file Vintage Wines-1): *Use formulas containing a range name to calculate prices in Great Britain Pounds, Euros and Japanese Yen.*
- Allows you to create names for single cells in a range. - It is easier to reference these cell range names in various formulas than using the standard row letter and column number. - This is particularly useful when dealing with multiple worksheets, as they are more descriptive.
Lesson 4-1: Automatically create single-cell range names (uses file Vintage Wines-1): What is the objective of this lesson?
1. Click: Formulas->Defined Names->Define Name. 2. Type KilometerToNauticalMile into the Name box: 3. Type: =0.539956803 into the Refers to box 4. Click the OK button
Lesson 4-2 (Distance-1): Given that one Kilometer = 0.539956803 Nautical Miles, create a named constant that will convert Kilometers into Nautical Miles.
1. Click the ConversionFactors worksheet. 2. Click in cell C4. 3. Type KilometerToMile into the Name box 4. Press the <Enter> key.
Lesson 4-2: Manually create single cell range names and named constants (Distance-1): *Manually create a range name for the Kilometer to Mile conversion factor using the Name box.*
1. The first objective is demonstrating how to create sing-cell range names manually, this is useful when you are unable (due to the makeup of the source range) to create the names automatically (using create from selection) 2. The second part is creating a named constant which basically is a reference that will never change (e.g. inches in a foot). Bear in mind that when you create a named constant, there is no cell on the worksheet that contains the constant value
Lesson 4-2: Manually create single cell range names and named constants (Distance-1): What is the objective of this lesson?
This lesson simply uses the single cell range name concept (learned in the previous two lessons) to make a formula more readable.
Lesson 4-3: Use range names to make formulas more readable (Sales and Profit-1): *What is the objective of this lesson?*
1. Select the range A8:I17. 2. Click: Formulas->Defined Names->Create from Selection. - Excel correctly guesses that the labels are in the Top row and Left column. 3. Click the OK button.
Lesson 4-4: Automatically create range names in two dimensions (Earnings Summary-2): *Create automatic range names based upon the labels in column A and row 8.*
1. Select the range A3:X26. 2. Click: *Formulas->Defined Names->Create from Selection*. - Excel correctly guesses that the labels are in the Top row and Left column. 3. Click the OK button
Lesson 4-5: Use intersection range names and the INDIRECT function (Distance Chart 1): *Create automatic range names based upon the labels in column A and row 3.*
4 Type From into cell A30. 5 Type To into cell A31. 6 Type Miles into cell A32. 7 Merge cells B30:E30, B31:E31 and B32:E32 (see lesson 4-6 of basic for refresher on this) 8 Add a list validation to cell B30 that will restrict the cell to the items appearing in cells A4:A26. (See lesson 2-11 of basic for refersher on this) 9 Add a list validation to cell B31 that will restrict the cell to the items appearing in cells B3:X3. 10 Click in cell B30 and choose Birmingham_ from the drop down list. 11 Click in cell B31 and choose Oxford from the drop down list.
Lesson 4-5: Use intersection range names and the INDIRECT function (Distance Chart 1): *Set up the fields that will be used to input formulas, and display the results from the distance chart.*
