Forum Discussion
Solved
EXCEL: Delete a row without lose references
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 e...
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?
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 #REFThe 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?