Cell reference changing by itself

**brian4excel** · 03-11-2015, 03:21 PM

Apologies for the extensive explanation. I have a large document that keeps track of attendance during specific periods that vary from person to person. (For instance, we're looking at Adam's attendance 1/1/15-6/30/15 but Bob's attendance 3/1/15-8/31/15.) One user enters daily attendance into a different workbook, and we use vlookups to pull it into the analysis workbook. We noticed that the data entry person sometimes accidentally caused problems with the formulas, and we sometimes needed to access at the same time, which is why we separated the two. All this to say that it's a pretty sizable document with a lot of references.

I recently noticed that some of the attendance data was off. It turned out that a few rows of the vlookups had shifted the cell reference for lookup_value by one row. I fixed the problem and sorted by percentage attendance, and I saw several rows switch to one row off (i.e., they sorted correctly, then the formula changed). I thought it was only a problem with the vlookups, but I also noticed that a simple sum column had several similar problems.

I tried searching, but I only kept coming up with basic troubleshooting that I had already tried. Is this a known issue? Can anyone provide guidance on how to solve it?

**gmr4evr1** · 03-11-2015, 03:30 PM

Have you tried $ in front of the column(s) and cell(s) ($A$1) that you don't want changing?

**brian4excel** · 03-11-2015, 04:12 PM

Thanks for the reply. To clarify, I do want the cell reference to change when I sort. If by sorting the row changes from 4 to 113, I would like the reference to change from A4 to A113. That's mostly what happens. But a few of the rows change the reference to A112 or A114. Or like in Row3 below: