Display Value from a cell in one tab to a cell in another tab, without having a formula in the display cell

boojooo

New Member
Joined
Nov 15, 2012
Messages
33
Hello,

I have a worksheet with two tabs, on the second tab I have all my data and on the first tab I have a blank list with headers. Once I input data on the second tab, in specific cell(s) (e.g. A1) I want that data to be displayed on the first tab in specific cell(s). The trick is I do no want to have any formulas listed in any of the cells in the first tab. Is this possible?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Obviously you can do this manually with a Copy then Paste Special / Value, but you seem to be looking for a more automated way to accomplish the same goal.

With that in mind, of course you can also "obviously" use VBA and have a macro do the Copy and Paste Special operations.

You could also set up data validation rules that only permit the one value that you want as an entry into the cell, but that's not quite automatic, either.

Finally, you can actually use a formula, but then use Cell and Sheet Protection and Hiding to hide the formula and prevent a not-very-determined user from discovering the formula that underlies the value he or she sees in the cell. That way, the value is captured in the easiest, most reliable automatic method (since formulas are as close to foolproof as we can get in Excel), but if the formula is hidden, the cell is protected, and the worksheet protection is turned on to prevent changes to those properties, then the user may not even know that there IS a formula.
 
Last edited:
Upvote 0
I can't believe I didn't think about protecting the sheet, thank you. Not sure why you can using "obviously" when replying to my thread though.
 
Upvote 0
I wasn't trying to be insulting or condescending. I was merely stating what I presumed would be an obvious fact to anyone who has the least exposure to Excel. "You can copy and paste values from one worksheet to another, or to another location in the same worksheet, and even to other workbooks and other applications." I apologize if my comment seemed to be in any way belittling; it was never intended that way.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top