# Turn off Formula Updating

K

#### Kjuib

I have a formula that calls for a range of cells.
but when I delete a row from this range, it changes the
formula to calibrate for this change. How do I get the
formulas to stay the same wheither someone adds or deletes
rows from the worksheet?

P

#### Peo Sjoblom

I guess you thought it was of no importance to show the formula you are
currently using?
Since you didn't I will assume it would be something like

=SUM(A1:A20)

now to get a formula always using the same range you can use indirect

=SUM(INDIRECT("A1:A20"))

G

#### Guest

Sorry I forgot to add the formula, I thought it might be
an option I could turn off somewhere.
The formula is:
=+VLOOKUP(\$A6,Arrangements!\$A\$2:\$F\$141,3,FALSE

Arrangements is the name of another worksheet in the file.
whenever I delete rows in Arrangements the \$F\$141 changes
and the 141 goes down.. 140, 139, 138... etc...

I tried to add the Indirect function, but it gave me an
error.
=+VLOOKUP(\$A6,Arrangements!indirect(\$A\$2:\$F\$141),3,FALSE)
(*the function you have typed contains an error*)

does anyone have any other ideas?

P

#### Peo Sjoblom

You have to apply it correctly

=VLOOKUP(\$A6,INDIRECT("Arrangements!\$A\$2:\$F\$141"),3,FALSE)

B

#### Bernard Liengme

Look at Peo's answer - there are quotes around the range in his but not in
yours!
Also you need to put the sheet name in the INDIRECT as in
=VLOOKUP(\$A6,indirect("Arrangements!\$A\$2:\$F\$141"),3,FALSE)
Note the + is not needed in formulas - are you a former Lotus-1-2-3 user?

Best wishes

G

#### Guest

That is what I was looking for. thank you.
-----Original Message-----
You have to apply it correctly

=VLOOKUP(\$A6,INDIRECT("Arrangements!\$A\$2:\$F\$141"),3,FALSE)

--

Regards,

Peo Sjoblom

.