Let's dive into the weeds of the mappings formula discussed in my earlier posts. Our goal is to translate fields without any hardcoded string literals or direct references to the fields. By dereferencing formulas, we make them adaptable to change and easy to copy/paste formulas without changing the references. First let's review the following mappings formula:
This formula uses the header to "map" another field in its row to a mapped value. The from=>to mappings are defined in the "Mappings" tab.
The first inner "Index" function gets the value in the "neighborhood" column. This is in LOC 4 and 7 (used to return the default value).
Here is the syntax highlighting from that LOC:
"MappingsHeaders" is a named range set to the first row of the "Mappings" tab.
The second inner "Index" returns the mapped-from range. This is what the offset in the original formula returned.
Here's the "neighborhood" range in the "Mappings" tab.
The superset "Match" returns the row number of the mapped-from "neighborhood" column in the "Mappings" tab.
And finally the first "Index" returns the mapped-to "FastSuperNeighborhood" value given three parameters: 1. "Mappings" named range, 2. row number of the "neighborhood" value in the "Mappings" tab, and 3. column number of the "FastSuperNeighborhood" column in the "Mappings" tab.
Altogether this "Mappings" formula is generic enough to be copy/pasted and fast-performing because it uses performant multi-threaded functions.
This formula uses the header to "map" another field in its row to a mapped value. The from=>to mappings are defined in the "Mappings" tab.
The first inner "Index" function gets the value in the "neighborhood" column. This is in LOC 4 and 7 (used to return the default value).
Here is the syntax highlighting from that LOC:
"MappingsHeaders" is a named range set to the first row of the "Mappings" tab.
The second inner "Index" returns the mapped-from range. This is what the offset in the original formula returned.
Here's the "neighborhood" range in the "Mappings" tab.
The superset "Match" returns the row number of the mapped-from "neighborhood" column in the "Mappings" tab.
And finally the first "Index" returns the mapped-to "FastSuperNeighborhood" value given three parameters: 1. "Mappings" named range, 2. row number of the "neighborhood" value in the "Mappings" tab, and 3. column number of the "FastSuperNeighborhood" column in the "Mappings" tab.
Altogether this "Mappings" formula is generic enough to be copy/pasted and fast-performing because it uses performant multi-threaded functions.
Comments
Post a Comment