21

Is it possible to reference a cell in Google Sheets so that cell where it gets displayed also displays it using the same text and cell colour formatting?

=A1

Will only reference cell's value. But if that particular cell has red background and white text I'd like that to be copied as well.

I'm leaning toward existing formula solutions rather than scripts. If applicable of course.

4
  • This site is for web applications only. Microsoft Excel isn't part of that. Furthermore, Excel uses VBA and Google Spreadsheets uses Google Apps Script for these kind of solutions. Please edit your question or else ask it on SU. Oct 8, 2013 at 7:42
  • @JacobJanTuinstra: I was looking forward to some already present formula I could use. And since Google Spreadsheets covers many many formulas present in Excel I added it as tag as well. But otherwise I know this is about web apps. I've seen several questions tagged with Excel anyways, hence my tag. But thanks. Won't add it in the future. Oct 8, 2013 at 7:57
  • 1
    Robert, there are many many differences between Google Spreadsheets and Microsoft Excel (2010). See answer I gave: webapps.stackexchange.com/a/44719/29140 Oct 8, 2013 at 11:02
  • 1
    @JacobJanTuinstra: So my many many is referring to those 85%. This proves that it does cover majority of formulas of Excel. :) And thanks for posting the link. Great insight. Oct 8, 2013 at 11:53

3 Answers 3

11

For Google Spreadsheets, it is possible by writing a script:

function copyValuesAndFormatting() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet();

    var fromRange = sheet.getRange("A2:A");
    var toRange = sheet.getRange("B2:B");
    var values = fromRange.getValues();
    var fontColors = fromRange.getFontColors();
    var backgrounds = fromRange.getBackgrounds();
    var fonts = fromRange.getFontFamilies();
    var fontWeights = fromRange.getFontWeights();
    var fontStyles = fromRange.getFontStyles();

    toRange.setBackgrounds(backgrounds);
    toRange.setFontColors(fontColors);
    toRange.setValues(values);
    toRange.setFontFamilies(fonts);
    toRange.setFontWeights(fontWeights);
    toRange.setFontStyles(fontStyles);
}

Add a trigger for the script function, so that it runs on every spreadsheet modification.

I have created a sample spreadsheet here. Feel free to copy it to your own account, and start experimenting with it.

7
  • I didn't state it clearly in my question, but I was more looking toward existing formulas and not scripts. If there's no formula combination to make this work, then your script would be much better if it was used as a formula called i.e. fullCellRef(cellReference) so one could use it as =fullCellRef(A1) for instance Oct 8, 2013 at 8:03
  • Ah, I see. But I don't think (correct me if I'm wrong) there's any formula that specifies formatting. Oct 8, 2013 at 8:05
  • I'd correct you if I definitelly knew hence my question. :) But otherwise. I suspect there's none anyway. So if you rewrite your script to be used as a cell formula I'll accept your answer because it would be best possible solution for the features at hand. Oct 8, 2013 at 8:06
  • 1
    Hmm, I currently don't know how a cell formula function could reference the cell it is used from, which is necessary in order to set formatting. I'll do some research. Oct 8, 2013 at 8:22
  • Nope, sorry, this seems impossible. A formula function does not have access to setting cell formatting. So I'll have to leave you with the trigger option. Oct 8, 2013 at 8:34
7
+100

Using Vidar and Jacob's answers as a basis I have created the following solution which will enable you to write =fullCellRef(A1) which will copy the value and format from A1.

A minor side effect is that if you drag-copy a cell with this formula, the new cells will initially copy the formatting of the original cell (as is normal), but will then switch to the referenced formatting after a small pause.

Sample sheet here.

/**
 * Dummy function to be the equivalent of using simple reference,
 * but is used to identify which cells to copy format.
 * The immediate effect of =fullCellRef(A1) is the same as =A1
 * 
 * @param  {string} value The value of the referred cell
 * @return {string}       The given value
 */
function fullCellRef(value){
  return value;
}

/**
 * For each cell with the formula eg B2=fullCellRef(A1), the format of
 * the referred cell (eg A1) is copied to the calling cell (eg B2)
 */
function copyFormatting() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var offsetRow = range.getRow() - 1;
  var offsetCol = range.getColumn() - 1;

  var formulas = range.getFormulas();

  var formats = {
    fontColors: range.getFontColors(),
    backgrounds: range.getBackgrounds(),
    fonts: range.getFontFamilies(),
    fontWeights: range.getFontWeights(),
    fontStyles: range.getFontStyles(),
    verticalAlignments: range.getVerticalAlignments(),
    horizontalAlignments: range.getHorizontalAlignments(),
    numberFormats: range.getNumberFormats()
  };
  var formulaIsUsed = false;
  for (var row = 0; row < formulas.length; row ++ ) {
    for (var column = 0; column < formulas[row].length; column ++ ) {
      var refersTo = findReferenceCells(formulas[row][column]);
      if (refersTo){
        formulaIsUsed = true;
        var refRow = refersTo.row - offsetRow;
        var refCol = refersTo.column - offsetCol;
        for (var key in formats) {
          formats[key][row][column] = formats[key][refRow][refCol];
        }
      }
    }
  }

  if (formulaIsUsed) {
    range.setBackgrounds(formats.backgrounds);
    range.setFontColors(formats.fontColors);
    range.setFontFamilies(formats.fonts);
    range.setFontWeights(formats.fontWeights);
    range.setFontStyles(formats.fontStyles); 
    range.setVerticalAlignments(formats.verticalAlignments);
    range.setHorizontalAlignments(formats.horizontalAlignments);
    range.setNumberFormats(formats.numberFormats);
  }

}

/**
 * Returns the 2D array indices to identify the referred cell.
 * @param  {string} formula The cell formula
 * @return {Array.integer}         The row and column array indices
 */
function findReferenceCells(formula) {
  if (formula === "") {
    return false;
  }
  var refPattern = /^=fullcellref\(([a-z]{1,2})(\d+)\)$/i;
  var matches = refPattern.exec(formula.replace(" ", ""));
  matches.shift();
  if (!matches) {
    return false;
  }
  // convert cell reference to array indices
  var column = colToInteger(matches[0]) - 1;
  var row = matches[1] - 1;

  return {row: row, column: column};
}

/**
 * Converts a column name to a column number
 * @param  {string} columnName eg "A", "BB"
 * @return {integer}            Between 1 and 256
 */
function colToInteger(columnName){
  var nameParts = columnName.toLowerCase().split();
  //97 is char code of "a", but we need 1 based indices
  var colNum = nameParts.pop().charCodeAt(0) - 96;
  if (nameParts.length === 1){
    colNum += 26 * (nameParts.pop().charCodeAt(0) - 96);
  }
  return colNum;
}
4
  • There is a error on Line 52 and 53 for Tom's script. Can somebody help to execute it properly.
    – user109923
    Dec 11, 2015 at 7:21
  • @SwapnilGosavi - I have just updated the code to include additional formats, and it seems to run correctly. Let me know if you still have issues Jan 28, 2016 at 6:56
  • 3
    This is pretty awesome. However, as I read the source code, this wouldn't work across tabs, correct?
    – Jade
    Aug 3, 2016 at 0:22
  • @Jade - No - it wouldn't work across tabs. It could possibly be made to do so, though I haven't really looked into it. Aug 3, 2016 at 0:31
3

This is the closest you can get, in having a formula feeling.

Code

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var aCell = sh.getActiveCell(), value = aCell.getValue();

  // get formatting
  var fontColor = aCell.getFontColor();
  var background = aCell.getBackground();
  var font = aCell .getFontFamily();
  var fontWeight = aCell.getFontWeight();
  var fontStyle = aCell.getFontStyle();
  var target = Browser.inputBox('Give column number, relative to active cell', 
    Browser.Buttons.OK);
  var tCell = aCell.offset(0,parseInt(target));

  // set formatting
  tCell.setBackground(background).setFontColor(fontColor).setFontFamily(font)
    .setFontWeight(fontWeight).setFontStyle(fontStyle).setValue(value);
}

Explained

Upon edit, a message box appears and asks for an input value (minus is allowed as well). Then the formatting (value included), as Vidar wonderfully already presented, is applied.

Example

Copied Vidar's file: Cell Formatting

1
  • Cool! Perhaps you could register your trigger as 'on modification', instead of 'on edit'. Thus, format-only changes will propagate as well. Oct 8, 2013 at 9:18

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