MrExcel.com - Your One Stop for Excel Tips & Solutions

Always Point To Cell B10

February 17, 2021 - by

Always Point To Cell B10

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.

Figure 37. This formula points to B10
Figure 37. This formula points to B10
Figure 38. The reference moves to B7 if you delete three rows above B10.
Figure 38. The reference moves to B7 if you delete three rows above B10.

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).

Figure 39. INDIRECT (“B10”) tells Excel to always look in cell B10.
Figure 39. INDIRECT (“B10”) tells Excel to always look in cell B10.

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 INDIRECT(“B10”).

Title Photo: Markus Spiske at Unsplash.com

This article is an excerpt from Excel Gurus Gone Wild.