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.

My new keyboard from Microsoft, the Microsoft Natural Ergonomic Keyboard 4000, while very useful and easy to type with has one big flaw. The function keys (F1 - F12) displayed along the top of the device lose their base functionality by default. The old functionality is replaced with “new” functions as assigned by Microsoft. Instead of getting in to how to deal with the issue here, I’ll let the cat out of the bad and just (more…)