Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formulas within list fail to update after sorting/filtering
Hello, I am having an issue and would like to know if other people
have encountered this and if there is a workaround or safeguard. I am using Excel 2010 but I am pretty sure in the past I've noticed this behavior in 2003 as well. I have a workbook in which I have a range of about 2000 rows and 30 columns. Many of the columns contain functions, some of which lookup values in ranges of other worksheets based on the unique identifier in that row. So in row 49 I have a function like =INDEX(sheet2!$b$1:$b$1000, match(A49, $a$1:$a$1000,0) This morning, after sorting and filtering that list various times, on another worksheet I saw a strange #NUM error. When I tracked it down, the cause was that in my worksheet formula above, some of the rows in that list had formulas that did not change. For example, that formula: may have been in row 72 instead of row 49. Or I might find the #NUM error in row 600, and the formula, which should reference cell D600, has some other row there like D41. I should mention that my workbook is set to manual calculation mode, but I believe that this movement of rows, and the subsequent updating of row references, should be separate from calculation. After all, it works for most of the 2000 rows, but for some reason certain rows are not updated as they should be. This is scary since unless a #NUM error pops up somewhere along the line, I might not have known anything was wrong, and the wrong looked up value could be used in a table or chart to be exported. Has anyone experienced that, and are there workarounds? Might it be a good idea to do an F9 recalculation after each sort or filter of a list? That would be very time consuming but I'd prefer that to having incorrect data. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas fail to calculate | Excel Discussion (Misc queries) | |||
Why would chart fail to update? | Charts and Charting in Excel | |||
Import link update filtering | Excel Discussion (Misc queries) | |||
why does the office update program allways fail | Setting up and Configuration of Excel | |||
trouble filtering a list. Why isn't column filtering? | Excel Worksheet Functions |