Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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

=A1

Will only reference dell'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.

share|improve this question
    
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. –  Jacob Jan Tuinstra Oct 8 '13 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. –  Robert Koritnik Oct 8 '13 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 –  Jacob Jan Tuinstra Oct 8 '13 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. –  Robert Koritnik Oct 8 '13 at 11:53

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
    
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 –  Robert Koritnik Oct 8 '13 at 8:03
    
Ah, I see. But I don't think (correct me if I'm wrong) there's any formula that specifies formatting. –  Vidar S. Ramdal Oct 8 '13 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. –  Robert Koritnik Oct 8 '13 at 8:06
    
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. –  Vidar S. Ramdal Oct 8 '13 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. –  Vidar S. Ramdal Oct 8 '13 at 8:34

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

share|improve this answer
    
Cool! Perhaps you could register your trigger as 'on modification', instead of 'on edit'. Thus, format-only changes will propagate as well. –  Vidar S. Ramdal Oct 8 '13 at 9:18

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()
  };
  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); 
  }

}

/**
 * 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;
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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