Search Excel for formulas

Let me apologise in advance to those that want to read about the exploits of ‘the boy’ but I’ve been documenting more work-related items here recently, mainly so that I can find them again later.

I’m currently working on a large project and the stage we are at requires multiple people to fill in a lot of data on an Excel spreadsheet. One of my tasks is to combine everyone’s work back into a single file, which is no big deal. The problem is that someone used a formula to gather some of their data and I made the mistake of copying their tab directly into the master file, instead of copying and pasting values.

Now when the file is opened it warns about referencing external data and I’d prefer that it not, plus there’s always a chance that something changes in the linked file. Rather than step through the many tabs looking for the offending cell(s) I turned to Google. It turns out that there’s no way to actually do this in one step but with a little effort you can work your way through it.

The first step actually worked for me, as this was a simple VLOOKUP function, but I’ll link the Microsoft article here as there’s much more that can be done.

I opened the destination workbook, which is all I actually had, and did Ctrl-F to open the Find dialog box. Select Options then enter [ in the ‘Find what’ field. We do this because external references will always be enclosed in square brackets. Then set the ‘Within’ to Workbook, ‘Look in’ to Formulas, and then click Find All.

For me this returned over 100 cells with a Formula, one long column of VLOOKUPs. Eliminating them is as simple as inserting a column, copying the cells with the formula, and pasting values into the new column. Delete the old column, save the file, and re-open to check whether the warning goes away. Rinse and repeat if necessary.

Microsoft’s article has the following choices:

Find external references that are used in cells

Find external references that are used in names

Find external references that are used in objects, such as a text box or shape

Find external references that are used in chart titles

Find external references that are used in chart data series

Cleaning up after a Mac

I was recently faced with the need to transfer some old files via USB so they could be centrally located and carefully vetted for future use, if any. Prior to copying them, via Robocopy and the command line of course, I took a quick look and saw lots of Mac hidden files.

They don’t really pose a problem but I wanted to get rid of them to speed up the transfer and to help my CDO. (That’s Obsessive Compulsive Disorder, but with the letters in their correct alphabetical order)

At a command prompt I changed to the network drive letter and typed the following commands:

del /s /q /f /a:h .DS_Store

del /s /q /f /a:h ._*

Just what the switches do can be found by typing /? after the command, so “del /?’ shows that:

/s will delete the specified files from all the subdirectories

/q is for quiet mode, it won’t prompt if you use a global wildcard

/f will force deleting of read-only files

/a:h will select files based on attributes, in this case hidden files.

A word of caution to those wishing to be wise. Typing “del .” and then hitting Enter is not a good idea. Any time you use “del” at the command prompt you are one keystroke away from A Really Bad Day™.

Upgrade! Don’t upgrade¡

With apologies to the Grateful Dead, anyone that’s been around IT for a while knows
“… what a long, strange trip it’s been”.

Yes, I’m talking about the browser best known for it’s use in downloading other browsers, Internet Explorer.

Microsoft keeps pushing that everyone needs to upgrade to the latest version, and there’s a lot of good reasons for that, mostly security focused. I can’t personally remember cleaning up after a virus infection that wasn’t through IE.

Not saying that MS makes it easy to upgrade anymore. I remember the days when you could download an executable file and upgrade computers without an internet connection to your hearts content. Today you have to find your way to Microsoft’s Download Center and then try and find what you’re looking for there, unless you’d like to take your chances with Googling for an IE installer. (Please don’t!)

It’s not too bad, to be honest, especially if you’re only worried about your home system. But what about when it’s a work computer, and a site you rely on requires a certain version of IE?

That’s when you wish you hadn’t upgraded, or had multiple computers, or unlimited virtual machines.

My latest calamity on this front is with that bastion of cutting edge technology, the U.S. Government, in the form of the Bureau of Alcohol, Tobacco, Firearms, and Explosives. Their electronic forms site lets you know that only IE and Safari are supported, but not IE after version 8. Or Firefox, or Chrome.

What to do if all you have is a current, supported version?

First, try Compatibility View, found under the Tools menu. This worked for me on this and other occasions. If you’re a home user, this is the simplest fix.

If you’re working in a domain environment, you may want to look at Enterprise Mode for Internet Explorer. I haven’t looked into this much yet, but it’s definitely on my radar.

Why can’t everyone do this?

I don’t admit this in public but, since most of my family isn’t exactly local to me, I figure it can’t hurt. Plus, if you’re reading this then you’re on my blog where I admit it anyway: I’m an IT Manager.

Information Technology. Stuff with a plug on the end of it, for the uninitiated.

I have a password manager with hundreds of different work-related sites: accounts here, there, and everywhere. And I have a smartphone with my work email on it.

If we lose power at work I need to know about it. There’s a number of reasons that I need to be contacted urgently for work and the phone stays on 24 x 7.

The problem? Most companies either don’t care or don’t realise that their emails, solicited, necessary, informational, or otherwise, come through at the most inopportune times.

Do I really need to be informed on a Saturday lunchtime that Comcast can “Make Everything Better”? Does Cisco Meraki really need to send out my monthly reports at 2 a.m. on the first day of the month, regardless of when that falls? Does this introduce the potential to miss something that is actually important?

No, No, and Yes.

There I am, then, configuring settings in my new WordPress.com blog when I stumble upon someone that actually gives you a choice.

2016-02-01_email-delivery

Thank you, thank you, thank you to the folks at WordPress.com for having the insight to build this in, I applaud you.

Now, off to schedule this post for the middle of the night so I can take advantage of low tariff rates on electricity and email postage.

that was easy

First step I could think of, now that I have a functioning WordPress install again, was to point my domain name over to this new site. A little trepidation as I had originally tried to use Google Sites to host a free public-facing website but that’s not as easy as it could be.

Off to my Google Apps admin console, then, and pull up the domain information. Not as easy as getting to, say, Drive or any of the other areas, but easy enough once you’re logged into your account. just go to https://domains.google.com

Everything you need to forward your domain is here on this first page, although I missed it first time around as I focused on the paid services.

2016-01-31_paid I jumped to the DNS page and couldn’t find what I needed there and ended up back on the Domain page where it hit me, like a big, green button:2016-01-31_forward

Click the green Forward button, type the URL, click the blue Forward button and Voila!

It took way longer to write up than it did to actually do, even including my initial incompetence.

Let’s do this again!

Time to give the website another shot; it’s a new year and priorities change constantly so it’s out with the old webhost, and in with something different. I don’t want to let my domain name go, but I don’t want to keep paying for something that isn’t giving me something in return which means a free WordPress site.

I’m going to try and put content up here related to my interests, whether it’s because I find it interesting and want to share or want to be able to find it later.

Stay tuned.