Excel Remove row without breaking formula 1

[klunde]

Hi

I have a spreadsheet with two sheets. On sheet two I have a list of formulas where the first 50 rows refers to one!a1..a50.

Now the problem:

I want my list on sheet two to ALWAYS refer to the cells a1 to a50, but if I on sheet one removes row 18, row 18 on sheet two ends up with a row saying #REF.

How can I avoid this?

</Morten>

</Morten>

 

[GlennUK]

Use the INDIRECT function to generate the references, like:
=INDIRECT("Sheet1!A1")

or maybe
=INDIRECT("Sheet1!A"&ROW())


Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

Hi,

If the row that you remove was reference by another cell, (quite often the previous or next row), then you must repair the formulas(s) affected: the one(s) containing the #REF! error.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

If in row x no references to cells in other row, then you can add/delete rows after selection of two sheets (with CTRL key).

combo

 

[klunde]

Thanks Glenn. The next thing would be to figure out how to apply an =INDIRECT() command on all my 597 existing formulas since you can't create an =indirect() and drag it to expand / increase the formulas.

SkigVought and Combo:

You didn't get the question.

</Morten>

</Morten>

 

[klunde]

Correction. The =INDIRECT("Sheet1!A"&ROW()) actually enables me to expand the formula.

</Morten>

 

[GlennUK]

Glad that I could help. :-D

Cheers, Glenn.

Beauty is in the eye of the beerholder.