SOLVED

# EXCEL: Delete a row without lose references

Occasional Contributor

# EXCEL: Delete a row without lose references

Hi,

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

# Re: EXCEL: Delete a row without lose references

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.

# Re: EXCEL: Delete a row without lose references

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

# Re: EXCEL: Delete a row without lose references

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?

# Re: EXCEL: Delete a row without lose references

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

# Re: EXCEL: Delete a row without lose references

The argument of INDIRECT must be a string:

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

# Re: EXCEL: Delete a row without lose references

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?

# Re: EXCEL: Delete a row without lose references

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

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

# Re: EXCEL: Delete a row without lose references

it works!!! thanks a lot man!