Displaying formula results...

[stollc]

Is there a way to keep all formulas on a seperate sheet, and point the results to a different sheet? For example I was the formula =1+1 on "Calculations" sheet, cell A1 and the result I want to be displayed on "results" sheet cell A1. I DON'T want any formula in the results cell.

Is this possible?

 

[Von Pookie]

Would something like this work for you?

Right-click the "calculations" sheet tab, and select 'view code.' Paste this code there. It should run whenever a change is made on the calculations sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Count > 1 Then Application.EnableEvents = True: Exit Sub

Sheets("results").Range(Target.Address) = Target

Application.EnableEvents = True

End Sub

 

[Zack Barresse]

Do you really need the extra sheet? Can't you just toggle between formula and regular view?? Do this by using Cntrl + ~

 

[stollc]

I just want to not have the formulas entered in the same cells that display the results. It probably isn't possible.

 

[Zack Barresse]

Not really. Not in the traditional sense. Here is a UDF that will show your formula ...

http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

You could use this on one sheet and keep your formulas in another. Is that what you are looking for?

 

[BuddieB]

My approach to this would be to put all the formulas on a "Calculations" sheet and then set the formatting for that sheet to show all formlas - CTRL + ~

Then make a "Results" sheet and link each cell to the Calculations sheet but do not show the formulas. in Cell A1 put ='Calculations'!A1 and copy that formla to all cells to shw the results

THis should achieve what you are looking for where one sheet displays the formula and the other displays the results.

 

[Von Pookie]

I just want to not have the formulas entered in the same cells that display the results. It probably isn't possible.

This works for me. Basically, you enter a formula, the result displays. The code will copy the cell you just entered the formula in and paste it into the same cell as values--displaying only the result, not the formula:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Target.Count > 1 Then Application.EnableEvents = True: Exit Sub

If Target.HasFormula Then
    Target.Copy
    Target.PasteSpecial Paste:=xlPasteValues
End If

Application.CutCopyMode = False
Application.EnableEvents = True

End Sub

 

[stollc]

Thanks guys! I will give those methods a try.