James Clear writes about using behavioral science to master your habits and improve your health. His free guide, Transform Your Habits, has been downloaded more than 80,000 times. This post originally appeared on his blog. We often measure our progress by looking forward. We set goals. We plan milestones for our progress. Basically, we try to predict the future to some degree. We do this in business, in health, and in life at large. Can we increase our quarterly earnings by 20 percent? Can I lose 20 pounds in the next 3 months? Will I be married by 30? These are all measurements…
Popular to-do list app Any.DO has given its Android app the usual bug-fixes and tweaks that come with app updates, but it has also gained an interesting new feature in the form of file attachments. While other to-do services such as Wunderlist have let you attach files to individual items for a while, Any.DO for Android is following suit. As you can see in the screenshots below, you’ll now see a little paperclip option when you click one of the items, and the little number that accompanies it rises to reflect the number of files attached. Any.DO version 2.87…
Scrolling through the usual list of on-demand video-streaming services on my PS3 last year, I couldn’t quite decide whether to fire up iPlayer to watch some documentaries, see what movies were going down on Blinkbox, or watch some random verbal enlightenment through TED’s YouTube channel. For reasons I can’t quite explain, my eyes were eventually drawn to one of the handful of ‘other’ services I’d habitually ignored in the TV/Video Services section, which include Animax and VidZone. “What the hell is MUBI?,” I wondered. It transpired that Mubi was a “…curated online cinema bringing you cult, classic, independent, and award-winning…
David Akka is the Managing Director of Magic Software Enterprises (UK). Application integration solutions have become key enablers for business success by allowing enterprises to increase competitiveness through streamlining business processes, increasing operational efficiencies and creating new innovative services. However, changing business environments and technologies, including the surging popularity of cloud-based applications, the shift to enterprise mobility and the need for real-time information mean that IT managers need to demand more from their application integration solution. Before investing in an integration solution, organizations should make sure that it will support both current and future requirements while still being be cost-effective to…
I’ve been playing around with the Formula Auditing tools a fair bit recently. These things:
In the course of this, I noticed a few things I haven’t before. Firstly, here’s my setup:
When I have cell D6 selected and click Trace Precedents, Excel is kind enough to draw just one arrow from the precedent range, while putting a box around the entire Precedent range so I can see where it is:
If I were to click on Trace Dependents for that cell, I’d like to see pretty much the same thing:
…but here’s what I actually see:
…which looks like something that Hippies were hanging on the walls in the late sixties:
…when they weren’t out protesting, that is:
Doing a Trace Precedents when there’s a much longer array of dependent cells involved looks even worse:
…and Excel becomes very sluggish as you scroll around, so presumably Excel is constantly redrawing these. Scrolling down doesn’t tell you much…that’s for sure:
Let’s take a look at another setup, to better illustrate a couple of things I didn’t know until now:
Here’s what Trace Dependents has to say about cell B2:
One thing I didn’t realise until today, is that if you keep clicking that Trace Dependent button, Excel keeps drawing in additional levels of downstream dependents:
In case you didn’t know, you can double-click on any of the blue arrows, and you’ll be taken to the Precedent/Dependent cell concerned…particularly handy if it points somewhere off-screen. And you can double-click the arrow once you’re there to be magically transported back again. The dotted arrow pointing to a little sheet icon in the above screenshot tells you that there’s an off-sheet dependent that points at cell C19, which you can jump to if you double click on that dotted arrow and then select the reference from the Go To box:
…although as you see above, the native dialog box is so narrow that you’re unable to actually see the cell addresses, and can’t be resized. In that case, you might want to download Jan Karel’s excellent RefTreeAnalyser, that fixes this and does a good deal more besides:
It also has a much better way of displaying precedents, by overlaying in the current window some little pictures of any precendents that happen to be out of view or on another sheet. (Would be really handy to have the same functionality for dependents too.):
Colin Legg has some great code that will also help you to determine all on-sheet and off-sheet precedent cells, using the .NavigateArrow method to actually travel up those blue arrows and thus find any precedents on other sheets. I imagine Jan Karel uses pretty much the same approach. [Edit: No, he doesn’t ]. You’ve got to use the .NavigateArrow method, because the Range.Precedents property doesn’t return precedents on other sheets or other workbooks.
Now here’s something nasty I’ve just noticed about the native Formula Auditing tool: It doesn’t pick up on off-sheet references that involve any kind of Table Reference, although on-sheet references work just fine:
So it is well broken, in my opinion, because I often refer to Tables on other sheets. And both Colin’s code and Jan Karel’s addin won’t help you here, I’m afraid. [Edit: Jan Karel’s code still catches these.] Seems to me the only way to get around this would be to search the worksheet for instances of a Table’s name occurring within formulas. That’s assuming there’s no way to actually read Excel’s dependency tree from wherever Excel maintains it. I seem to recall seeing a post a few years back about how you can extract information from the tree by extracting XML from the workbook file, but that might just be a flight of fancy. Anyone know whether it ispossible to interrogate the dependency tree directly somehow?
How ’bout colours instead of Arrows?
Given all those arrows can get pretty confusing, I thought I’d have a crack at coding up something that lets you use Conditional Formatting instead and/or arrows to highlight Dependents (Green) and Precedents (Blue). Here’s my starter for ten, with both Dependents and Precedents highlighted. Direct Dependents/Precedents get a darker colour and white bolded font so you can easily tell them from indirect:
…and you can restrict it to just showing direct:
…and overlay arrows if you want:
It automatically updates if you change your selection, too:
It doesn’t solve the Table issue mentioned above, but I’ve been finding it quite handy to get a quick feel for what’s going on in those crappy spreadsheets I inherited…err…designed recently.