When using named ranges for pivot tables (among other things), I find myself constantly having to update the area of the spreadsheet referred to by the named range. This renaming occurs as a result of my adding new raw data to the table, or editing the table’s Column/Row layout. When researching possible solutions, I came across this article at Ozgrid. The gist of it is to utilize the “OFFSET” function (more…)

Excel may be asking you to update links when you do not even know you have any. Below is a strategy to detect links you may not be aware of:

1. Unhide any hidden Worksheets first. Then right click on any sheet name tab and select “Select all sheets“. Now go to Edit>Find and in the Find What: box type: [*] also select Formulas from the Look in: box and click Find Next. This will find any formulae referring to another Workbook.
2. Go to Insert>Name>Define and scroll through the list to ensure no named ranges are referring to another Workbook.
3. Go to each sheet and ensure you have no linked Pictures, Charts or Controls.
4. Go to Edit>Links and select the file name under Source file, now click Change Source… In the “Change Links” dialog locate the Active workbook, ie the one you are in now, select it and click OK. This will force the Link to refer back to itself. If you get an error go to step 5!
5. Open a new Workbook, save it as any name. Create a real link to it from you problem Workbook. Now go to Edit>Links and select the file name (not the newly created file!) under Source file, again click the Change Source… button but this time select the newly created Workbook from the “Change Links” dialog and click OK. Now Save the Workbook and delete the real link you created and save

If this strategy does not work, go to HERE and download the: Download Dellinks.exe. This article is a reproduction of the information found here at www.ozgrid.com.

Here is a list of useful Excel websites and blogs. from my Del.icio.us account. They have been tremendously resourceful over the years. Once on Del.icio.us you can use the tags “Access”, “Visio”, “Word” and “Windows” for other useful sites related to those programs. Enjoy………..