4

I have a formula in a cell to calculate a value. But I want the value to be shown only when the user clicks on the cell.

I looked around but there seems to be nothing by default.

2 Answers 2

3

A hack might be to make the font colour in the cell the same as its background colour, so nothing is visible 'normally' but on clicking the cell the contents would appear - in the formula bar. Otherwise I think it would require a script but I take it that is not what you want since your question is not tagged that way.

1
  • 1
    To expand on pnuts's hack, you could have a script that auto-populates a note in the cell that will be visible on hovering or clicking that cell.
    – AdamL
    Oct 10, 2014 at 0:58
3

As @pnuts already mentioned, a script is needed. But also this script will not give you exactly what you want. The onEdit() trigger is only triggered when something has changed and there's no onClick() event trigger available in Google Apps Script.

Therefore I've created this small snippet for you to use.

Code

// global
var ss = SpreadsheetApp.getActive();
var app = SpreadsheetApp.getUi();

// create menu and set all to white
function onOpen() {
  ss.getDataRange().setFontColor('white').setBackground('white');   
  app.createMenu("Cell").addItem("Check value", 'cellValue').addToUi();
}

// retrieve value and show it
function cellValue() {
  var cell = ss.getActiveCell(), A1Notation = cell.getA1Notation();
  var value = cell.getValue();
  var text = "The value of cell " + A1Notation + " is: \n\n" + value;
  app.alert(text).CLOSE;
}

Explained

The above code will create an extra menu entry open opening of the file. It uses the same technique @pnuts describes, by setting the font- and background color to white. Selecting a cell and activating the menu option, will show a box with the corresponding value.

Example

I've created an example file for you: Show cell value on click
Make a copy of my file to access the code or add the code under Tools\Script editor. Press the bug button to authenticate the script and you're on the go !!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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