How do I keep cell C5 the data source despite if I insert copy rows?

I would like to always know show what is cell B5, in another cell C3, but when I insert, let's say 2 rows, C3 S show what is in B7. Is there a formula to prevent this

excel

Formulas and Functions

EmmaSmyth
Nov 06, 2024
You can achieve this by using the `INDIRECT` function in Excel. The `INDIRECT` function returns the reference specified by a text string. This way, even if you insert or delete rows, the reference will remain constant.

In your case, you want cell C3 to always show what is in cell B5, regardless of any row insertions or deletions. Here’s how you can do it:

1. Go to cell C3.
2. Enter the following formula:
=INDIRECT("B5")

This formula ensures that cell C3 will always display the value of B5, no matter how many rows you insert or delete.

EmmaSmyth

Brass Contributor

Nov 06, 2024

You can achieve this by using the `INDIRECT` function in Excel. The `INDIRECT` function returns the reference specified by a text string. This way, even if you insert or delete rows, the reference will remain constant.

In your case, you want cell C3 to always show what is in cell B5, regardless of any row insertions or deletions. Here’s how you can do it:

1. Go to cell C3.

2. Enter the following formula:

=INDIRECT("B5")

This formula ensures that cell C3 will always display the value of B5, no matter how many rows you insert or delete.