SOLVED

EXCEL: Delete a row without lose references

Occasional Contributor

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

8 Replies
best response confirmed by FlavioSorbo (Occasional Contributor)
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.

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

@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?

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

@FlavioSorbo 

The argument of INDIRECT must be a string:

 

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

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?

@FlavioSorbo 

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

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

it works!!! thanks a lot man!