I have a Google Sheet that has its data pulled in from another sheet. The destination sheet has text that is formatted (not conditional formatting) - for example the headings are one colour text and the notes are another. This formatting is obviously done on the destination sheet as I cant get formatting to pull through from the original. However when ever additional data is added or removed on the original sheet and therefore updated on this sheet, the text colour all changes - so it stays in the original cell and isn't specific to the text. Is there any easy to fix to this?
123198
I am importing a certain range of data from a master sheet and would like to maintain the formatting for those cells.   It seems as though this only works within a sheet: . This would be the perfect option, except I am unsure how I can reference the master sheet's key.
75236
I have imported a webpage table in to Google Sheets. It frequently being updated. A series of calculations has been conducted on the sheet named Shawn Of the link spreadsheet.  I have filtered the my desired results into the Calculation sheet of that linked document... which is in columns A5:A. I then applied =ARRAYFORMULA(if(A5:A200=&quot;&quot;,,A5:A200*{12,13,37,101,111})) in columns B5. The Results of the formula extends from B5:F. This results from a5:F was subsequently conditionally formatted to highlighted duplicates in the aforementioned, i.e. a5:f using the lists of values in K5:K and I5:5. In Columns A5:F, is it possible to generate/place values in the range A5:f with matching name of states and aligned values in J5:K and H5:I?
95594
In Excel it is possible to style text within a cell, by pressing Crtl+1. You can create for example the following sequence: 1,2,3,4.  Is that possible in Google Spreadsheets?
82033
I'm looking to have cell borders automatically applied when a cell is not empty. I thought I could use conditional formatting for this, but it doesn't seem to be an option.
83488
I'm running into this weird problem with Google spreadsheets: if the cells have comments on them and I sort the spreadsheet, the rows sort but the comments stay in the same position, and don't move with the cells they're attached to.  In other words, the cells look like this:  A C  &lt;-- comment on this cell B   and then I sort by this column:  A B  &lt;-- comment remains on this cell (stays in position) C  &lt;-- comment should have remained attached to this cell   This means that sorting is completely broken and I can't use it at all, because I clearly need the cell comments to stay with the cell they're attached to.  Is this a bad bug in Google Spreadsheets, or am I missing something here?
121540
I've been referencing  to help me calculate the sum of cells based on their background color in Google Sheets. From the comments and some other research it appears that changing the color doesn't trigger a change in the cell. This causes my function to never update.  Does anyone know a way to force my sum function to update when just the background color of a cell in my range changes? I've tried changing the update settings under "spreadsheet settings" already, that doesn't work.
26464
I have a spreadsheet that I need to add new data to but the previous user made it so that I can't add data in a new row without moving down a lot of data which would mess with all the calculations.  What I am trying to do is to make it so I can add the new data to the side instead and still have the same drop down access the two separate columns. I attached a screen cap of an example of what I want below.  I have tried everything I can think of and all my searches come back with putting multiple columns in a drop down instead of just the data from them.  As you can see below the example has data in D1 to D5 and F1 to F5. If I wanted the dropdown to have the data from those columns how would I do it?  EDIT: Just to clarify there is data between the columns in the actual page which is why I can't just highlight all of it.
111556
I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between.  function LinInt(x){       var ss = SpreadsheetApp.getActiveSheet();   var cell = ss.getActiveRange();   var hue;   hue = (x/100)*120;    var color = HSVtoRGB(hue, 40, 100);   cell.setBackground(color);    return x;     }     function HSVtoRGB(h, s, v) {     var r, g, b, i, f, p, q, t;     if (h &amp;&amp; s === undefined &amp;&amp; v === undefined) {         s = h.s, v = h.v, h = h.h;     }     i = Math.floor(h * 6);     f = h * 6 - i;     p = v * (1 - s);     q = v * (1 - f * s);     t = v * (1 - (1 - f) * s);     switch (i % 6) {         case 0: r = v, g = t, b = p; break;         case 1: r = q, g = v, b = p; break;         case 2: r = p, g = v, b = t; break;         case 3: r = p, g = q, b = v; break;         case 4: r = t, g = p, b = v; break;         case 5: r = v, g = p, b = q; break;     }   return '#'+((b | g &lt;&lt; 8 | r &lt;&lt; 16) / 0x1000000).toString(16).substring(2); }   When I do this I get an error saying that I am not allowed to call setBackground. I assume this is because I'm not allowed to change the color of one cell from another because I'm screwing up getting the current cell that the function is running in.  How do I get the cell that the function is running in so that I can call setBackground() on it?  For example, I'd like to type =LinInt(50) into cell A1 and have A1 be yellow and have the number 50 in it. Then when I type =LinInt(100) into cell A2 it is green and has the number 100 in it.
1694
I have a formula in a cell which counts the values in 2 other cells. Normally, after entering in the values in these 2 other cells, the formula cell updates automatically when Enter is pressed or when the cells are clicked out of.  However, for some reason, the value calculated by the formula does not update automatically. I am having to click on the formula bar, then I have to click enter for the formula cell to do its thing.  Why is this happening?
28536
I have a Google Spreadsheet that contains a script to count a number of cells by color. The script works perfectly but seems like when ever I change the color of the cell, the count is not updated. How to update/refresh the cell count?  The script I'm using is from :  function getBackgroundColor(rangeSpecification) {   var sheet = SpreadsheetApp.getActiveSpreadsheet();   return sheet.getRange(rangeSpecification).getBackgroundColor();      }  function sumWhereBackgroundColorIs(color, rangeSpecification) {        var sheet = SpreadsheetApp.getActiveSpreadsheet();   var range = sheet.getRange(rangeSpecification);        var x = 0;        for (var i = 1; i &lt;= range.getNumRows(); i++) {     for (var j = 1; j &lt;= range.getNumColumns(); j++) {                var cell = range.getCell(i, j);                if(cell.getBackgroundColor() == color) {         x += parseFloat(cell.getValue());       }     }   }        return x; }  function countCellsWithBackgroundColor(color, rangeSpecification) {        var sheet = SpreadsheetApp.getActiveSpreadsheet();   var range = sheet.getRange(rangeSpecification);   var x = 0;   for (var i = 1; i &lt;= range.getNumRows(); i++) {     for (var j = 1; j &lt;= range.getNumColumns(); j++) {       var cell = range.getCell(i, j);       if(cell.getBackgroundColor() == color) {         x++;       }               }   }        return x; }
70976
How can I automate a find &amp; replace function to change the font colour of all suit symbols document wide?  I write bridge articles (card game), and I need to automate this formatting constantly:  Find all "♣" and color the text green (#00b700). Find all "♦" and color the text orange (#ff8100). Find all "♥" and color the text red (#ff0000). Find all "♠" and color the text blue (#0000ff).  Answered: Mark and Michael helped build Google Apps Scripts to do this (see below). .
98425
I have a column which should define in list if the issue is in status: -Not Started, Active, Completed.   I would like to ideally add some colour to this by the row for example if completed it is green or active it is red. Then it highlights the whole row, do you know if this can be edited within the columns settings or if there any step by step instructions that can be followed to make this change? (ideally for someone new to sharepoint)  Thank You
28623
I'm not sure what is happening in my Google Sheet query and could really use some help. I have  that demonstrates and explains the issue.   Basically, I'm using a query to pull data from a Google Spreadsheet that is populated by a Google Form. If the form submits only numbers (100), there is no problem. However, if someone submits a string ($100 or 100 dollars), the query will not display the string. It will only display form submissions that are 100% numbers.   Also, if the 1st submission is a string (not 100% numbers) the query will work.   Here is my query  =query(data!B2:CL,"select B, C, D, E, F, G, H, I, J, CI, BZ, CA, BA  where J = 'Industry Certification'",false)   Any ideas on why this is happening?  Also, I'm aware that I can limit the form to numbers only. I'm trying to avoid that and learn 'why' this is happening.
116355
In an Excel spreadsheet, I'd like to automatically update a cell with the current date and time when another cell is changed (like an update timestamp).  There is a timestamp for each row. I'd like to update them as soon as one of the preceding cells in the row is updated.  Do you have any pointers on how to do that?
98022
When I paste text from another source with "Keep source formatting option" into PowerPoint 2010 I receive a white background, that I don't want.    How can I make this text background transparent, so there would be just red text on purple?
119305
I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's . People can add tasks to column B and I will assign them to the right people in the team, filling in the other columns. The first field I'm capturing is the date that a new task is logged. I'd like to automate this so that when people add a new task starting in column B, column A gets updated with the date that the new line is created.   I've found scripts that will update the timestamp in column A based on changed in column B, but I only want to log the creation date. In other words, when there is already an entry in column B and it gets updated, the date in column A should not change. Only when column B changes from an empty cell to a new value should column A on that row be updated with the date.  In the above example, if I change the task on line 2, the date in column A should not change. If I add a new task in cell B3, it should add the current date in cell A3.
111439
I have a list of 200 cells in Excel. I put in names and would like the text color in the cell or the background of the cell to change according to the name written. I use about 25 names.