Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating data makes formula wrong
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. -- a_moron ------------------------------------------------------------------------ a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671 View this thread: http://www.excelforum.com/showthread...hreadid=505266 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating data makes formula wrong
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" wrote in message ... 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. -- a_moron ------------------------------------------------------------------------ a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671 View this thread: http://www.excelforum.com/showthread...hreadid=505266 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating data makes formula wrong
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: http://www.excelforum.com/member.php...o&userid=30671 View this thread: http://www.excelforum.com/showthread...hreadid=505266 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating data makes formula wrong
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating data makes formula wrong
Thank you *vezerid*, that was exactly what I was looking for :) -- a_moron ------------------------------------------------------------------------ a_moron's Profile: http://www.excelforum.com/member.php...o&userid=30671 View this thread: http://www.excelforum.com/showthread...hreadid=505266 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match then lookup | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula Result Correct but value in the cell is wrong | Excel Worksheet Functions |