software

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 lt;---- Wrong (A8 instead of A6)
5=A9*0,25 lt;---- Wrong
11=A10*0,25 lt;---- Wrong
=A11*0,25 lt;---- Wrong
--------------------If the amout of rows should change to a lower amout of rows the result
in the formula goes quot;=#REF!quot;

I have tried with the $A$1 -gt; $A$9 but it still changes after a update.--
a_moron------------------------------------------------------------------------
a_moron's Profile: www.excelforum.com/member.php...oamp;userid=30671
View this thread: www.excelforum.com/showthread...hreadid=505266Hi

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.

quot;a_moronquot; gt; wrote in
message ...
gt;
gt; I don't know if there is a easy way to fix this?
gt; Maybe some settings or maybe a change in the formula?
gt;
gt; The problem is when I refresh my data (query) the formula adjust it
gt; self depending on the amout of rows of data I get after the refresh.
gt;
gt; A simple example:
gt; Code:
gt; --------------------
gt; Column:
gt; _A_ _B_
gt; 14 =A1*0,25
gt; 9 =A2*0,25
gt; 5 =A3*0,25
gt; 19 =A4*0,25
gt; 4 =A5*0,25
gt; =A6*0,25
gt; =A7*0,25
gt; =A8*0,25
gt; =A9*0,25
gt; --------------------
gt; The value in column A is a value I get from a database when refreshing.
gt; (5 values in the above example, but the formula goes to row 9)
gt;
gt; After a refresh I get new data containing 8 rows of data and result of
gt; the formula is as follows:
gt; Code:
gt; --------------------
gt; Column:
gt; _A_ _B_
gt; 14 =A1*0,25
gt; 9 =A2*0,25
gt; 5 =A3*0,25
gt; 19 =A4*0,25
gt; 4 =A5*0,25
gt; 8 =A8*0,25 lt;---- Wrong (A8 instead of A6)
gt; 5 =A9*0,25 lt;---- Wrong
gt; 11 =A10*0,25 lt;---- Wrong
gt; =A11*0,25 lt;---- Wrong
gt; --------------------
gt;
gt;
gt; If the amout of rows should change to a lower amout of rows the result
gt; in the formula goes quot;=#REF!quot;
gt;
gt; I have tried with the $A$1 -gt; $A$9 but it still changes after a update.
gt;
gt;
gt; --
gt; a_moron
gt;
gt;
gt; ------------------------------------------------------------------------
gt; a_moron's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30671
gt; View this thread: www.excelforum.com/showthread...hreadid=505266
gt;

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.--
a_moron------------------------------------------------------------------------
a_moron's Profile: www.excelforum.com/member.php...oamp;userid=30671
View this thread: www.excelforum.com/showthread...hreadid=505266One 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
Thank you *vezerid*, that was exactly what I was looking for --
a_moron------------------------------------------------------------------------
a_moron's Profile: www.excelforum.com/member.php...oamp;userid=30671
View this thread: www.excelforum.com/showthread...hreadid=505266