Updating data makes formula wrong

[a_moron]

I don't know if there is a easy way to fix this?
Maybe some settings or maybe a change in the formula?

The problem is when I refresh my data (query) the formula adjust it
self depending on the amout of rows of data I get after the refresh.

A simple example:
Code:
--------------------
Column:
_A_ _B_
14 =A1*0,25
9 =A2*0,25
5 =A3*0,25
19 =A4*0,25
4 =A5*0,25
=A6*0,25
=A7*0,25
=A8*0,25
=A9*0,25
--------------------
The value in column A is a value I get from a database when refreshing.
(5 values in the above example, but the formula goes to row 9)

After a refresh I get new data containing 8 rows of data and result of
the formula is as follows:
Code:
--------------------
Column:
_A_ _B_
14 =A1*0,25
9 =A2*0,25
5 =A3*0,25
19 =A4*0,25
4 =A5*0,25
8 =A8*0,25 <---- Wrong (A8 instead of A6)
5 =A9*0,25 <---- Wrong
11 =A10*0,25 <---- Wrong
=A11*0,25 <---- Wrong
--------------------


If the amout of rows should change to a lower amout of rows the result
in the formula goes "=#REF!"

I have tried with the $A$1 -> $A$9 but it still changes after a update.

 

[Guest]

Hi

I have had this on many occasions. The only absolute sure way I've found of
making sure it's right is to refill the formula down from the top. I know
it's a pain, but it's the only solution I've found.

Hope this helps.
Andy.

 

[a_moron]

Thats what I'm doing right now
I'll try to put the input (updated) data in a new sheet (tab) and do
the formula on another sheet and see if that helps.

 

[vezerid]

One way your formulas in column B:B will not be affected by
insertions/deletions which you do not possibly control: In B1:

=OFFSET(B1;0;-1)*0,25

And you can copy down as far as necessary.

HTH
Kostis Vezerides

 

[a_moron]

Thank you *vezerid*, that was exactly what I was looking for