Excel – Security Warning: Automatic update of links has been disabled [Fix]

Okay, so not quite a fix. But, we have a number of different Excel spreadsheets that we use to build our system, solution, and proposal quotes structures with the lot of them tying in to each other.

Somewhere along the line while editing one of them the following error started to appear an a leaf workbook (Trunk = full proposal, Branch = hardware, software, or labour, Leaf = Individual components/servers/etc.):

Security Warning: Automatic update of links has been disabled. Enable Content

Okay, so how do we find these links?

A quick search turned up the following methodology:

1. Highlight all sheets by CTRL+Clicking on them.
2. CTRL+F to bring up Find
3. Click the Options button.
4. Change Within to: Workbook
5. Look in: Formulas
6. Find What: [
7. Click the Find All button.
• 

Ah, now it becomes clear as to what happened to bring about the error. An errant copy and paste.

Now that we know where the problem is, we can fix it and no longer receive the warning for this particular leaf workbook.

Hat Tip: Excel Hints: Find (and Delete) External Links

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book

*Our original iMac was stolen (previous blog post). We now have a new MacBook Pro courtesy of Vlad Mazek, owner of OWN.

Windows Live Writer

Excel 2010 – Multiplies Cells Containing Formula Results with a Slightly Off Result?

Okay, this is just plain weird:

That calculation is taking a raw number “8” and multiplying it by the result of a series of calculations found in the second cell with the result of “$20.55”.

The end result states that 8 * 20.55 is 164.38 which for most of us seems to be really wrong. Especially for a program that is supposed to do this right!

So, just how do we “fix” this?

A quick search turned up the culprit:

Under File –> Tools –> Advanced we find the Set precision as displayed setting.

When we enable it and click OK Excel becomes quite unhappy about it:

 Microsoft Excel

Data will permanently lose accuracy.

Well, be that as it may, that “loss in accuracy” helps the old human brain to see what it really needs to see:

 

That is the expected result! This works for all versions of Excel too.

The solution was found here:

• CPearson.com: Rounding Errors In Microsoft Excel 97

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book

*Our original iMac was stolen (previous blog post). We now have a new MacBook Pro courtesy of Vlad Mazek, owner of OWN.

Windows Live Writer

Excel 2008 Mac Excel 2007 PC Version Weirdness

Probably the one biggest hiccup that happens when working on the same Excel 2007 native (XLSX) spreadsheet at one point on the MacBook Pro and then on the PC is a mysterious margin change.

We have a number of fairly complicated quotation sheets that we have built in Excel on the PC.

When we set up the quote on the PC, then go in and tweak it later on via the MacBook Pro, the MacBook refuses to PDF or print the spreadsheet in the same manner as the PC.

For some really strange reason, the Mac will shift the right margin to the left by two columns (1 Pt each) and thus refuses to print the page out as we would expect.

Here is a screen clip of one of our quote sheets in Print Preview mode on a PC:

Note the fact that there is a border on the right hand side one column to the right of the Amount column. In the above screen clip, the Mac would actually clip off the column to the left of the bold boundary as well as the Amount column.

Go back to the PC though, and we will see the above properly formatted printout in the same worksheet after working on it with the MacBook Pro.

The other bit of weirdness that happens is the intermittent disabling of the auto-calculate feature in the Excel spreadsheet.

While working on this evening’s quote the sheet was mysteriously not updating any changes made. It took a few seconds and a settings check to figure out that the auto-calculate was turned off. This was after the quote was worked on while on the MacBook Pro.

When it comes to a multi-platform setup where both Macs and PCs are used to collaborate on projects, it is a really good idea to be familiar with these types of hiccups between platforms.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists
Co-Author: SBS 2008 Blueprint Book

*Our original iMac was stolen (previous blog post). We now have a new MacBook Pro courtesy of Vlad Mazek, owner of OWN.

Windows Live Writer

Excel - Clean Up the Div/0 Errors in a Worksheet

We are setting up some custom quoting setups in Excel.

One formula that we have is: D20/E20.

If nothing is in the respective cells we get an error: #DIV/0!

Excel does not like to divide by zero.

To correct the error, we need to use a conditional formula on top of the above. We use the following Macro to change all of the affected cells:

Dim rng As Range, cell As Range, fmla As String
Set rng = Cells.SpecialCells(xlCellTypeFormulas, 16)
For Each cell In rng
fmla = Right(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=if(iserror(" & fmla & "), """"," & fmla & ")"
Next

The procedure is as follows:

1. Click on the Office Jewel and then the Excel Options button
2. Find and check "Show Developers tab in the Ribbon"
3. OK
4. Click the Developer tab
5. Click Macros
6. Type a name: CleanUpDiv0 [Enter]
7. Click Edit
8. Paste the above Macro in between the Sub and End Sub lines.
9. Close MS Visual Basic (should save automatically)
10. Click the Macros button
11. Make sure the CleanUpDiv0 is selected and click Run

The Div/0! errors will now be gone!

A screenshot of a properly formatted macro in MS Visual Basic:

Excel Clean Up Macro

This tip comes to us courtesy of Escalus near the bottom of the page on the MrExcel.com site: #Div/0.

One can also bring about the same results in Excel 2003 via the Tools menu.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.

Mac on SBS - Excel:Mac and Excel Windows Inconsistencies

We are in the process of putting together the final draft of a set of technical assessment forms that we will be using for client's job candidates.

The forms are in Excel. They were created in Excel on Windows.

When we create forms in Excel, we always set the column width to 1 for enough columns to hit the .25" margins across the page. We modify it accordingly for forms that need to have .5" margins.

This gives us the flexibility to create forms with fields that are not restricted in their placement because of one big column. If the form is for print only, we don't bother merging the respective fields. If the form is for live data input via a laptop, then the respective cells will be merged and the range labeled.

When we open this particular form in Excel:Mac, we end up with the form being 4 columns (4 points) too wide. The margins and page size are identical on both the Mac and Windows PC. But, something within the Mac version of Excel says the number of columns is too many to fit on the page.

To make it work in Excel:Mac, we have to scale the "Adjust to" 94% in the page setup, and define a page break between the two pages.

They sit side by side, so the page break goes on the left boundary column on the page that sits to the right.

This was definitely unexpected, as we have been working with Word documents back and forth between the Mac and Windows platforms with no issues so far other than constraining Office 2007 to save in the legacy non-XML format.

Philip Elder
MPECS Inc.
Microsoft Small Business Specialists

*All Mac on SBS posts are posted on our in-house iMac via the Safari Web browser.