Solved

Excel Index Formula

Posted on 2013-06-18
3
317 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

707 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

14 Experts available now in Live!

Get 1:1 Help Now