Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am having an issue in that I am using various formulas to move, split data, etc from various sources. The problem is when my final results post to the final destination that I want, I still need to either run advanced filters, or a vlookup with the results. I can’t do this because as an example

if cell A1 shows a value of: A127

the actual cell content is: =RIGHT(A2,FIND(" ",A2&" ")-2)

Everything I read said to copy and paste special values, but this doesn’t work for me as the idea is to have the formulas/macros run everything and eliminating cutting and pasting.

In the case above I have a formula that pulls that info from a spreadsheet that is saved every week. Once it is pulled part of it is cut out in another column. I then need to run a vlookup on those results for data already contained on another tab.

share|improve this question

migrated from Mar 28 '12 at 13:48

This question came from our site for professional and enthusiast programmers.

I suggest you try "locking" your cell references before copying and pasting.

Enter into your cells so that you can edit them, click on the cell reference, and press F4 to toggle through the different expressions:

  • $A$2 means that whenever you copy the formula elsewhere, it will always point to cell A2
  • $A2 means that it will always refer to column A, but if you paste the formula on a different row, the reference will shift appropriately
  • A$2 means that it will always refer to row 2, but if you paste the formula into a different column, the reference will shift

You can also manually type in the $ sign or use find/replace to quickly edit a list of cells Eg:

 Find:          (A
 Replace With:  ($A$
share|improve this answer

You must log in to answer this question.