MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


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.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.