D
david rocha
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.
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.