excel - send result of formula to another cell.

Factual Questions
1

Is it possible to have the results of a formula go to a cell other than the cell containing the formula?

And I don’t want to do the ‘=c5’ type thing. That’s the wrong way round (that’s another cell requsting the data from the forumla cell. I need the formula cell to pro-actively send the results to the other cell, so the other cell can contain the actual data)

This is so that if someone does ctrl-c on the cell and they copy the value rather than the formula.

2

You could write a Macro that inserts the formula and then automatically Copy->Paste Values the values in the other column.

Or just alert folks to Paste-> Value when they copy!

3

You can do it using visual basic but I couldn’t tell you how anymore.

4

for starters you could make the reference cell locked. =$c$5. That way it will bring the results with it on a simple copy and paste.

5

I won’t say that it’s impossible to do what you want to do, but I *can *say with authority that:

  1. A formula in a cell cannot push content into another cell

  2. A VBA macro for a user-defined function cannot change the content of any cell, it can only return a value as a result

Now, it might be possible to do what you want to do, if you can describe what you actually need to do. (Many users start to take an incorrect path and then ask a very specific question to pursue that solution. It is usually better to state the overall problem so you can get help finding the best solution.)

6

On second glance, it is possible to do this only with a macro, and you can’t create it with the macro recorder.

The macro would be a handler for the Change event for the worksheet with the cell containing the formula. The sub would look like:



Private Sub Worksheet_Change (ByVal Target As Range)
   Range("CellWithValue").Value = Range("CellWithFormula").Value
End Sub

This code assumes that both cells are on the same worksheet, and that each one has a named range*. It will run no matter what cell changes on the sheet. You can make it more selective, depending on how you use the data, what the formula is, what else is going on, etc., etc.

In addition you want to put some controls on the cell containing the actual formula. Either put it in a hidden column, or lock it and protect the sheet against selecting locked cells. Depends on whether you want it to be visible.

*You could also do it like this if you know the cell references aren’t going to change:



Private Sub Worksheet_Change (ByVal Target As Range)
   Range("$B$1").Value = Range("$A$1").Value
End Sub

where $B$1 and $A$1 the cells corresponding to the first code sample.