Turn off Formula Updating

  • Thread starter Thread starter Kjuib
  • Start date Start date
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?
 
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"))
 
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?
 
You have to apply it correctly


=VLOOKUP($A6,INDIRECT("Arrangements!$A$2:$F$141"),3,FALSE)
 
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
 
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




.
Click to expand...
 
Back
Top