Excel has been around for decades, so it isn’t surprising that there are many features tucked away under the hood. What is surprising is when a useful feature is lost and only careful archeology can bring it back to life.
Excel 4.0 had a rudimentary macro language, mostly using excel formula approaches to building functionality. This was replaced by the VBA programming language. But there are still useful little items tucked away in this early language that haven’t been replaced.
One of these is Get.Cell.
Get.Cell had a boatload of switches that allowed the user to pull information about the cell formatting and contents and most of these have been replaced by the Cell and Type functions in Excel.
But one piece of information that Cell and Type can’t tell you is whether your cell or cells contain formulas vs values and sometimes this is a very handy thing to know at a glance. For example, if you build a spreadsheet using formulas to estimate amounts; but then start to drop in values as more concrete information becomes available.
In this situation I like to format cells containing formulas differently from the cells containing values, so that I can see at a glance where my estimates are. Its’ handy to have the formatting change automatically, so I don’t have to remember what my rules are weeks or months later.
This is where Get.Cell shines. The syntax I’m going to use is
=GET.CELL(48,A1) – where A1 is the cell I’m going to reference.
The trick here is that Get.Cell is NOT entered in a cell, but instead as a named formula. After creating the named formula, I can reference it while applying conditional formatting. In this way, when the type of content in the cell changes the conditional formatting automatically updates.
Where does one find information about the Get.Cell function? Not from Microsoft or at least not easily from Microsoft.
Try this post https://www.mrexcel.com/forum/excel-questions/20611-info-only-get-cell-arguments.html to see the possible switches for Get.Cell
First published in 2017, the Get.Cell function remains useful today. I remain hopeful that MS will incorporate its’ functionality in newer versions of Excel. If you want expertise in Excel, drop me a line at catharine@mytechgenie.ca