Displaying formula results...

stollc

Board Regular
Joined
Jun 14, 2004
Messages
56
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?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
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.

Code:
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Do you really need the extra sheet? Can't you just toggle between formula and regular view?? Do this by using Cntrl + ~
 

stollc

Board Regular
Joined
Jun 14, 2004
Messages
56
I just want to not have the formulas entered in the same cells that display the results. It probably isn't possible.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

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

Board Regular
Joined
Aug 6, 2003
Messages
174
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
stollc said:
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,467
Members
414,452
Latest member
Dannysamworth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top