Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com Mar 28 '12 at 13:48

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

add comment

1 Answer 1

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.