A Non-Volatile INDIRECT Alternative in Excel using the Pub/Sub Pattern

A Non-Volatile INDIRECT Alternative in Excel using the Pub/Sub Pattern

The INDIRECT function in Excel is a tricky beast. One the one hand it can be incredibly useful, but on the other hand it is responsible… A Non-Volatile INDIRECT Alternative in Excel using the Pub/Sub Pattern. Dramatically improve spreadsheet performance and decouple your ...

A brief explanation of the INDIRECT function

Image for post

Using INDIRECT to get a value from another workbook

The INDIRECT function takes a cell address and returns the value contained within the cell.

When designing a spreadsheet or set of spreadsheets it pays off to plan ahead and keep them well organized. Conceptually, using INDIRECT combined with Named Ranges can seem like a great way to do that. You can keep one area of functionality in one workbook and share key results with other dependent workbooks by looking up those values with INDIRECT.

Using named ranges avoids hard-coding explicit address references and allows us to refactor or restructure the referenced workbook later.

Why using INDIRECT is terrible for performance

The INDIRECT function is a volatile function. This means that every time anything in your workbook changes or any time you press F9 to calculate, the INDIRECT function will be called. On its own this may not be such a big deal, but because the INDIRECT function is called repeatedly and calculations that take the result of that as an input will also be called repeatedly.

If the result of the INDIRECT call is an input to some complex calculation or slow function then your spreadsheet will crawl. Every time you change anything the entire calculation will be re-done, even when the change you’ve made has nothing to do with that part of the spreadsheet.

Excel maintains a dependency graph which enables it to know what cells need recalculating after any changes have been made. This allows it to do the minimal number of computations when recalculating a worksheet after a change has been made. This is a very efficient way of minimizing the work that needs to be done so that spreadsheets can update quickly. Using INDIRECT ruins this as anything that is dependent (directly or indirectly) will end up be recalculated every time Excel recalculates.

python excel computer-science

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Space Science with Python — A very bright Opposition

Space Science with Python — A very bright Opposition. How does the brightness of an asteroid change? What are the dependencies and are there any amazing effects?

Basic Data Types in Python | Python Web Development For Beginners

In the programming world, Data types play an important role. Each Variable is stored in different data types and responsible for various functions. Python had two different objects, and They are mutable and immutable objects.

Space Science with Python - Bright Dots in the Dark Sky

Space Science with Python - Bright Dots in the Dark Sky - The tutorial describes another important basic concept in Space Science: the brightness of objects.

Space Science with Python - Uncertain Movements of an Asteroid

Today (30th June) is Asteroid Day and we will start to deep dive into the science of asteroids.

How To Compare Tesla and Ford Company By Using Magic Methods in Python

Magic Methods are the special methods which gives us the ability to access built in syntactical features such as ‘<’, ‘>’, ‘==’, ‘+’ etc.. You must have worked with such methods without knowing them to be as magic methods. Magic methods can be identified with their names which start with __ and ends with __ like __init__, __call__, __str__ etc. These methods are also called Dunder Methods, because of their name starting and ending with Double Underscore (Dunder).