Sorted data values changed

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I sort data in a large file, the formula commands in the same cell of
every row are changed in the resorted data, returning a VALUE# message,
appropriately because of the changed formula. This is my formula:
=IF(AND(Misc!$D$2-'Enrollment
Detail'!$AF5>360,AM5<>"RHC",AM5<>"Withdrawn",AM5<>""),"Completed","")
I am in the Enrollment Detail worksheet. The $AF5 does not change with the
rest of the row's cells. When all the others change to say row 10, the $AF
reference will change typically to one row less, is $AF9. This only impacts
moved data. So the rows above row 9 in the resorted data are not
Excel 2007 help suggests resorting the same data, but that does not correct
the problem. Other note: these cells are also all linked to a validation
list on the worksheet Misc.
 
Pam said:
=IF(AND(Misc!$D$2-'Enrollment
Detail'!$AF5>360,AM5<>"RHC",AM5<>"Withdrawn",AM5<>""),"Completed","")
I am in the Enrollment Detail worksheet.
Click to expand...

Remove the "'Enrollment Detail'!" from the formula before $AF5, make
sure all the row references are to the same row as the row containing
the formula and copy this formula to all the other cells in that column
of the table and I will bet it will behave as you expect

Bill Manville
MVP - Microsoft Excel, Oxford, England
 
Thank you for responding. I had already tried your suggesting (and many
other ideas), and it did not work. However, the cell referenced in the
beginning of the clause on the Misc page is actually a TODAY function. So,
this morning I decided to replace the Misc!$D$2 reference with TODAY(), then
I eliminated the then unnecessary Enrollment Detail worksheet reference. Now
it works. I have resorted the data and get no errors. That's great, but it
should have already worked the other way and I still do not know why it
didn't. Excel automatically inserted the Entrollment Detail worksheet
reference when I selected the cell from within the worksheet.
 
Back
Top