up vote 1 down vote favorite
share [g+] share [fb]

I'm not quite sure of the terminology involved in this problem. What I have to do is calculate in column B some values dependent on the current contents of column A. This I do with a formula as there are a lot of rows (the formula is =90 - A1).

Then, given this new value in column B, to set the value in column A to the new value of column B (using =B1). This gives me zero in all cases (rather than the circular reference I was expecting).

What I'd like to do is fix the current value of column B as it has been calculated, so I can then set the value of column A as I like. Is there any way to do this?

Edit: the values must end up in columns A and B, although I can scribble in any other column as necessary.

link|improve this question
89% accept rate
1  
Do you need the result value in Column A? I've never used Excel that much, but I'd go for a more procedural approach and introduce a new Column C. What you can do is copy the whole Column B and paste it using the raw values instead of the formula. – slhck May 20 '11 at 9:45
Use paste special, only paste the value of column B. – gunbuster363 May 20 '11 at 9:53
@Gunbuster, @slhck, thank you; using paste special and an extra column has done it. @slhck, perhaps you could put that in as an answer. – Brian Hooper May 20 '11 at 9:58
feedback

2 Answers

up vote 5 down vote accepted

As said in the comments before, if you just want the values and not have them depend on any formula, do as such:

  1. Copy the whole column B
  2. Select the same cells again and paste (using right click and "Paste special" or by clicking at the tooltip that appears after pasting)
  3. Select "Values"

This effectively removes the formula behind the cell and lets column A use the raw value of column B again.

Also, I've seen more procedural approaches where you would just use an extra column C that depends on the values generated in B — unless you need the column A.

link|improve this answer
feedback
up vote 3 down vote

Just to clarify, you want to apply a formula to cells in column B (which the formula refers to cells in column A) then after that only to make all value in column A = column B?

If this is the case, I would suggest that you try these steps:

  1. Apply formula to column B
  2. Select affected cells in column B
  3. Right-Click -> Copy
  4. Right-Click -> Paste Special
  5. Select Values instead of All (this step should paste only the current value instead of the formula)
  6. Click OK
  7. Apply =B1 to all cells in column A

These steps should give you the results that you were looking for.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.