Microsoft Excel Hacks !!

Hack #13 Remove Phantom Workbook Links
Ah, phantom links. You open your workbook and are prompted to "Update Links," but there are no links! How can you update links when they don't exist?
External links are links that reference another workbook. Unexpected external linking can occur for various reasons, many of them stemming from moving or copying charts, chart sheets, or worksheets into another workbook. Knowing why they're there doesn't always help you find them, though. Here are a few ways to deal with the spooky phantom link problem.
First, you need to see whether you have any real external links (nonphantom) that you forgot about. If you are not sure whether you have real external links, start looking in the most obvious place: your formulas. You can do this by ensuring no other workbooks are open and then searching for [*] within the formulas on each worksheet. Close all other workbooks to ensure that any formula links will include [*], where the asterisk represents a wildcard string.
Note: Excel 97 doesn't provide the option of searching the entire workbook, but you can search all worksheets in a workbook by grouping them. You do this by right-clicking any Sheet Name tab and choosing Select All Sheets. In later versions of Excel, Find… and Replace… provide the option of searching within the sheet or workbook.
Once you find the formula links, simply change the formula accordingly or delete it altogether. Whether you change the formula or delete it depends on the situation, and only you can decide which route to take.
You also might want to consider going to the Microsoft Office Download Center, located at http://office.microsoft.com/Downloads/default.aspx, and from the Add-Ins category selecting the Delete Links Wizard. The Delete Links Wizard is designed to find and delete links such as defined name links, hidden name links, chart links, Microsoft query links, and object links. However, in our experience, it does not find phantom links.
Once you're confident there are no formula links, you need to ensure that you don't have any nonphantom links lurking somewhere else. To do this, we like to start from within the Excel workbook containing the phantom links. Select Insert -> Name -> Define. Scroll through the list of names, clicking to highlight each one and looking in the Refers To: box at the bottom. Check to make sure none of these names is referencing a different workbook.
Note: Instead of clicking each name in the Define Name dialog, you can insert a new worksheet and select Insert -> Name -> Paste. Then, from the Paste Name dialog, click Paste Link. This will create a list of all the names in your workbook, with their referenced ranges in the corresponding column.
If any of the names are pointing outside your workbook, you've found the source of at least one link that would prompt the updating question. Now it's up to you to decide whether you want to change this range name to refer only to the workbook itself or leave it as it is.
Another potential source of links is in your charts. It's possible that your charts have the same problem we just explained. You should check that the data ranges and the X-axis labels for the chart aren't referencing an external workbook. Once again, you get to decide whether the link you've found is correct.
Links also can lurk in objects, such as text boxes, autoshapes, etc. Objects can try to reference an external workbook. The easiest way to locate objects is to select any single cell on each worksheet and then select Edit -> Go To… (F5). From the Go To… dialog, click Special and then check the Objects option and click OK. This will select all objects on the worksheet. You should do this on a copy of your workbook. Then, with all objects selected, you can delete, save, close, and reopen your copy to see whether this has eliminated the problem.
Finally, the last not-so-obvious place to check for real links is in the hidden sheets that you might have cleverly created and forgotten about. Unhide these sheets by selecting Format -> Sheet -> Unhide. If the Unhide option on the Sheet submenu is grayed out, that means you have no hidden sheets. (If you think there are sheets that don't turn up in the menu, see "Hide Worksheets So That They Cannot Be Unhidden" [Hack #5] for more information.)
Now that you have eliminated the possibility of real links, it's time to eliminate the phantom links. Go to the haunted workbook with the phantom links and select Edit -> Links…. Sometimes you can simply select the unwanted link, click Change Source, and then refer the link back to itself. Often, though, you will be told that one of your formulas contains an error, and you will not be able to do this.
If you can't take the easy way out, note to which workbook Excel thinks it is linking (we'll call it the well-behaved workbook). Create a real link between the two by opening both workbooks. Go to the problem workbook and, in any cell on any worksheet, type =. Now click a cell in the well-behaved workbook and press Enter so that you have a true external link to the other workbook.
Save both workbooks, but don't close them yet. While in the phantom links workbook, select Edit -> Links… and use the Change Source button to refer all links to the new workbook to which you just purposely created a link. Save your workbook again and delete the cell in which you created the true external link. Finally, save your file.
This often eliminates the offending phantom link, as Excel now realizes you have deleted the external link to the workbook. If this does not solve the problem, however, try these next steps, but make sure you save a copy first.
Warning: The following process involves deleting data permanently. Therefore, before you begin, create a backup copy of your workbook. Neglecting to do so could create new problems for you.
With the problem workbook open, delete one sheet, Save, and then close and re-open the workbook. If you are not prompted to update your missing links, the sheet you deleted contained the phantom link. This should solve the problem, but if it doesn't, repeat the first step for each sheet in the workbook. You will need to add a new sheet before you delete the last sheet, as any workbook must have at least one sheet.
We're going to assume this technique worked for you. So, here's what you should do next. Open the copy of your workbook (the one that still has data in it) and make another copy. You've got to work with the problem worksheet (or worksheets) and use the process of elimination to discover where the problem is in the worksheet.
With the problem worksheet active, select a chunk of cells (about 10x10) and then select Edit -> Clear -> All. (Are you absolutely sure you saved a copy?) Save, close, and reopen the worksheet. If you are not prompted to update those links, you found the problem and your reward is to redo that block of cells. If you are prompted to update the links, continue deleting cells until you aren't are no longer prompted. Then redo the badly behaved cells.
We hope these techniques will save you some of the frustration that arises when those dreaded phantom links appear in your workbooks. They're not easy or fun to perform, but they can get you out of trouble.
Hack #70 Find the nth Occurrence of a Value
Excel’s built-in lookup functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the nth occurrence of specified data. Fortunately, there are ways to make Excel do this.
You can use Excel’s lookup and reference functions on a table of data to extract details corresponding to a specified value. Perhaps the most popular of these Excel functions is VLOOKUP. Although VLOOKUP is great for finding a specified value in the leftmost column of a table, you cannot use it to find the nth occurrence in the leftmost column.
You can, however, use a very simple method to find any specified occurrence you choose when using VLOOKUP, or one of the other lookup functions.
For this example, we will assume you have a two-column table of data, with column A housing first names and column B their corresponding ages, as shown in Figure 6-12.

Figure 6-12. Data setup for VLOOKUP.
You can use a VLOOKUP function to extract a person’s age based on his name. Unfortunately, some names occur more than once. You want to be able to look up the name Dave and have the VLOOKUP function find not the first occurrence, but rather, subsequent occurrences of the name. Here is how you can do this (remember, in this example, data is in columns A and B).
First, select column A in its entirety by clicking the letter A at the column head, and then select Insert -> Columns to insert a blank column (which will become column A). Click in cell A2 (skipping A1 because B1 is a heading), and enter this formula:
=B2&COUNTIF($B$2:B2,B2)
Copy this down as many rows as you have data in column B (click back in cell A2 and double-click the fill handle). You will end up with names such as Dave1, Dave2, Dave3, etc., as shown in Figure 6-13. Note the absolute reference to $B$2 in the COUNTIF function and the use of a relative reference for all references. This is vital to the function working correctly.

Figure 6-13. Data with VLOOKUP formula added to column A.
If you haven’t guessed already, now you can use column A as the column to find the nth occurrence of any name.
The formula will return the age for the third occurrence of the name Dave, as shown in Figure 6-14.

Figure 6-14. Data with second VLOOKUP formula added to column D.
You can, of course, hide column A from view, as you do not need to see it.
You also can use the names in column A as the Source range for a list in another cell by selecting Data -> Validation -> List. Then reference the cell housing this list in your VLOOKUP function.