I'm working on updating some overly complex and inefficient formulas in an Excel workbook. When doing so, I noticed one of the formulas that calculates a check figure wasn't working. After looking at the problem I noticed that the lookup range in the VLOOKUP columns was starting two columns to the left of my data. I remembered adding new columns to the left of the data previously and wondered why the references didn't update as the columns shifted. My calc mode is set to automatic and I'm using Excel 2013. I tested this with a quick example in a blank workbook and adding columns caused the formulas to update. Here is an example of the formula (please don't judge, I didn't write this and I will fix it using a UDF that I wrote, I just want to know why Excel isn't acting as expected).
So for reference, all the AH's should be AJ's and all the CJ's should be CL's. I could find and replace them, but I'm not keeping the formula anyway, I just wanted to know why the column references didn't shift with the data.
After initially typing this up, I thought, maybe I selected the range and manually moved it over rather than adding columns, so I tested that on my simple example and noticed that doing that would not update cell references, even if they were not absolute references. So then I tested adding an entire column to my actual workbook and only the second reference in the range, the CL reference, updated. The first remained AH. This makes no sense to me. Can someone educate me?
Code:
=ROUND(VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,12,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,11,FALSE)+VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,27,FALSE)+VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,27,FALSE)+VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,29,FALSE)+VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,29,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,37,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,41,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,43,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,45,FALSE)+VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,47,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,49,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,51,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,53,FALSE)-VLOOKUP("GRAND TOTAL",$AH$62:$CJ$16055,55,FALSE),2)So for reference, all the AH's should be AJ's and all the CJ's should be CL's. I could find and replace them, but I'm not keeping the formula anyway, I just wanted to know why the column references didn't shift with the data.
After initially typing this up, I thought, maybe I selected the range and manually moved it over rather than adding columns, so I tested that on my simple example and noticed that doing that would not update cell references, even if they were not absolute references. So then I tested adding an entire column to my actual workbook and only the second reference in the range, the CL reference, updated. The first remained AH. This makes no sense to me. Can someone educate me?
