• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Excel Index Formula

How can I force Excel not to hold the $M1:$M1234 relativity when I insert a new column?  I have TONS of formulas that are written this way.  Now my boss wants me to insert a column in the worksheet which is throwing off all of my results.  

Also, once the results are thrown off, they return a REF in the formula that I have to manually update.  Can I simply highlight the entire sheet and replace "REF!" with the correct column range.

ugh...
0
Feisty472
Asked:
Feisty472
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Can you give a bit more detail on the problem you are facing? Normally such a thing does not happen in excel. What is the formula which is failing when a column is added?
0
 
FaustulusCommented:
The #REF error will replace a cell reference when the column to which it refers is deleted, not added. Your example $M1:$M1234 will change to $N1:$N1234 if a column is inserted before column M.
In order to prevent the #REF error when deleting a column that is referenced you can use this little trick. Say, you want to delete column M and want the existing reference to column M all refer to column E.
- Insert a blank column E
- Cut values from column M
- Paste to column E
All references to column M will have changed to column E, like $E1:$E1234. You can now delete column M without creating any #REF errors.
I hope this helps.
0
 
Feisty472Author Commented:
This helped!  Thanks...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now