Solved

Excel Index Formula

Posted on 2013-06-18
3
328 Views
Last Modified: 2013-06-23
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
Comment
Question by:Feisty472
3 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39258068
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
 
LVL 14

Accepted Solution

by:
Faustulus earned 500 total points
ID: 39262033
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
 

Author Closing Comment

by:Feisty472
ID: 39270110
This helped!  Thanks...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now