EXCEL: Delete a row without lose references

[FlavioSorbo]

Hi,

i would like to know how delete a row without loose the references about this row.

I give you an example, if i have a formula with some cell of row 64, and i want delete it, i would like that excel understand to update the reference with the new row below.

Thank you so much

[#2 · Accepted solution]

@FlavioSorbo 

You can use the INDIRECT function for this:

=INDIRECT("E64")

will always refer to cell E64, even if you delete row 64, or insert or delete rows above it.

[#3]

Ok thank you, I've already created the formulas, it's possible to add the function INDIRECT massively?

[#4]

@FlavioSorbo 

What do your formulas currently look like? Simply =cell_reference or more complicated?

And which cell references should be changed? Only those that refer to row 64, or all references, or something in between?

[#5]

yes in think it doesn't work with me, i have formulas like this =IF('TEMPLATE_data entry'!E$10<>"",'TEMPLATE_data entry'!E$10,""), i want delete the row 10 in the "TEMPLATE_data_entry", but if i put the formula in this way:

=IF(INDIRECT('TEMPLATE_data entry'!E$10)<>"",INDIRECT('TEMPLATE_data entry'!E$10),"")

I get #REF

[#6]

@FlavioSorbo 

The argument of INDIRECT must be a string:

=IF(INDIRECT("'TEMPLATE_data entry'!E$10")<>"",INDIRECT("'TEMPLATE_data entry'!E$10"),"")

[#7]

oh, I see, do you know if there is a way to insert this characters INDIRECT(".....") into a formula?
I explain you what i've done:
1) with find and replace i transformed the formula in a string just found = and replaced with £=
2) with find and replace i transformed the formula like that
£=IF(INDIRECT("'TEMPLATE_data entry'!A$6<>"",INDIRECT("'TEMPLATE_data entry'!A$6,"")

Now i have to put ") in every reference, do you know some ways?

[#8]

@FlavioSorbo 

Perhaps replace <>"" with ")<>""

And replace ,"") with "),"")

[#9]

it works!!! thanks a lot man!