Absolute table column references need a syntax update
Structured references are often preferred to cell references because they add a dose of readability and memorability.
Let’s be honest, you’d rather see tblEmployees[Name] than A2:A11, wouldn’t you?
A few months ago, I talked about how it would be good if we were able to toggle between relative and absolute table column states with a simple press of the F4 key.
For example, this would allow us to go from tblEmployees[Name] to tblEmployees[[Name]:[Name]] and vice versa.
Now, I want to focus specifically on absolute table column references.
These are necessary to prevent ‘slippage’ in formulas, so references stay put when you drag the fill handle sideways.
However, not only do we not have a keyboard shortcut to switch from the relative type, but there is no shorthand syntax, either.
For tblEmployees[Name], this means having to repeat the column reference ([Name]), sandwich a colon (:) in between them, and enclose it all in an extra pair of square brackets ([]). That’s an additional nine characters to give tblEmployees[[Name]:[Name]].
The bloat is most felt when you have multiple and lengthy occurrences of absolute table column references in formulas.
I suggest a syntax update is necessary so we can reference in a more succinct way that is less clumsy and cumbersome.
Naturally, the dollar sign ($) comes to mind when envisaging how this could be done.
What about these?
tblEmployees[$Name]tblEmployees$[Name]
I would argue the first is better, as the symbol sits directly next to the column name, mimicking how absolute cell references are denoted. However, $ is not currently a prohibited starting character in names, so that would probably have to change.
Regardless, both are an upgrade on what we currently have, and either would be much welcomed.
Do you want to see this as well?
