Thread: Excel 2007 changing formula references?

I hope this is a simple fix. I built a workbook on Excel 2003, and it works great. Basically, the problem section is in the formula that has a VLOOKUP to another sheet. I use the range L2:M1000, with 1000 just as a dummy number because the records grow weekly (maybe by 2 or 3). Right now there are only 540 records, but each time I re-open the workbook, the formula gets changed to only how many records are on the sheet I'm referencing.

So in summation, my lookup that uses L2:M1000 will change to L2:M540 when I open it. This would be fine, except when new records are added, it stays as L2:M540.

Is there any way to "lock" in a range to a formula and force it to keep it?

The approach I use for this is always to reference Named Ranges that adjust themselves with the Offset formula. This post should get you up and running.

That fixed it! Thanks so much.