Always Point To Cell B10
February 17, 2021 - by Bill Jelen
Challenge: You want to create a formula that always points to cell B10. Normally, if you have a formula that points to B10 or even $B$10, the formula changes if you cut and paste B10 or if you insert or delete rows above row 10.
In Figure 37, a formula in D1 checks to see if a value is in cell B10. In Figure 38, even after items are deleted from rows 6, 8, and 9, the formula still reports an item in B10, even though cell B10 is empty. This is because the formula has changed to point to B7 in Figure 38.
Solution: You can use
INDIRECT(“B10”) to ensure that the formula always points to cell B10. Even if you delete or insert rows, and even if you cut B10 and paste to B99, your formula will always point to B10 (Figure 39).
Gotcha: If you are a fan of formula auditing, note that the Trace Dependents and Trace Precedents commands do not recognize the relationship between cell B10 and the formula in Figure 39. If you use Trace Dependents from cell B10, Excel will report that there are no dependents.
Summary: To force a formula to always point to cell B10, you can use
Title Photo: Markus Spiske at Unsplash.com
This article is an excerpt from Excel Gurus Gone Wild.