I have Google Sheet 1 with a result in a cell that will turn green or red based on conditional statements that evaluate if the result is good or bad. On sheet 2 I would like to have post PASS or FAIL based on the color of the cell in sheet 1. Any help?
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.
102134
I have a simple Sheet with 2 cells that I need to countif 2 conditions match and treat  duplicates as 1.  Column A    Column B Male        closed Female      invalid # Male        exception Male        closed Male        closed Female      new   I would like a formula that counts Male closed and return 1 result (ignoring duplicates)
108546
How do I Change cell color in Excel 2010 for upcoming or past dates based on 90 days from today. I would be great if I could use 2 color scale but one color will be fine.
87666
I want to check whether a color has been previously defined, consider the below code which doesn't execute.  \documentclass{article}  \usepackage{xcolor} \newcommand{\colorprovide}[2]{\ifx\undefined{#1}\colorlet{#1}{#2}\fi}  \begin{document}     %Initial Definition     \colorprovide{mycolor}{red}     \textcolor{mycolor}{Test 1}      %Will overwrite     \colorlet{mycolor}{green}     \textcolor{mycolor}{Test 2}      %Should NOT overwrite as it exists     \colorprovide{mycolor}{red}     \textcolor{mycolor}{Test 3} \end{document}   In other words and analogous to \providecommand{...} not renewing an existing command if it exists, I need a macro which will will NOT redefine a color if the color has already been defined.  The above code throws an error as the logical checking part \ifx\undefined... is incorrect.
53858
Is there any way to display a sheet name as the result of a function in a Google Spreadsheets (exactly like , except in Google Sheets)?
98548
What is wrong with this code?  = IF (F3+E3+D3 &gt; 0, F3+E3+D3, 0)   I want the cell to contain the sum only if it is positive. If it is negative I want zero to be there.  Google Spreadsheet gives me error: Parse error.
36136
In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1):  A | B 1 | x 2 |  3 |  1 | x 1 | x 4 |  8 |  // x can be any value and is here merely to mark the cell that should be formatted   IMPORTANT 2014 NOTE: Conditional formatting based on formula that may include other cells is now possible in Google Sheets, and works very similarly to how Excel spreadsheets work.  explains its use.
121286
I'm trying to create a formula in a Google spreadsheet which looks like this:  if (x &lt; 0, x + 1, x)   It means if x is smaller than 0, return x + 1, else return x.  However, x itself is an expression, e.g. A1 + B1. So I ended up with:  if ((A1 + B1) &lt; 0, (A1 + B1) + 1, (A1 + B1))   How can I save the expression (A1 + B1) into a temporary variable x such that I can do this? :  x = (A1 + B1); if (x &lt; 0, x + 1, x);   The current expression in my spreadsheet looks like this:  if(     timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))     &lt; 0,     1 +     timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10))))     ,     timevalue(Sheet1!$D10)-timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10)))) )   I'm trying to get it to look shorter and more manageable like this:  x = timevalue(Sheet1!$D10) - timevalue(min(filter(Sheet1!$D$2:$D$99,Sheet1!$A$2:$A$99=A10,Sheet1!$E$2:$E$99=E10)))); if(     x     &lt; 0,     1 +     x     ,     x )
51128
I have a column with function values of True or False:  =IF(ISNA(A1), false, true)   from row 1 to 5000+ rows. I would like to mark all the False as red background color or text as red. Is there any function available?
113025
I would like to extend the following example so that cells with YES in are colored green and cells that have NO in are colored red:  \documentclass{article} \usepackage{pgfplotstable}  \begin{filecontents}{data.csv} features,model1,model2,model3  feature1,YES,NO,NO feature2,NO,YES,YES \end{filecontents}  \begin{document}  \pgfplotstabletypeset[col sep=comma,string type]{data.csv}  \end{document}   Thanks
37393
The usual way to reference a sheet is  but how do you reference a sheet when the sheet's name is a value in a cell?  For example,   A1 = "Sheet number two" B1 = A1&amp;"!B4"   Here I want B1 to display the value in 'Sheet number two'!B4 but it's not working for me.  Any ideas?
34585
I have a workbook in Google Sheets and it is for a schedule for my company so I have a few sheets in my workbook that have the days of certain weeks and then one more tab that has inventory information. What I am looking to be able to do is type in the WO number into one of the week sheets and then type that into the WO spot on the inventory tab and in that same row on the inventory sheet there will be check boxes we click on that will change the color of the cell on the schedule so we can see what's happening with the WO on the schedule and don't have to shift over to inventory sheet every time we want to see where a house is at in the process. I hope that make sense and I greatly appreciate the help! Here is a link to it:
73446
I have a sheet with a couple hundred rows. Each row has an 'owner' and a pass/fail field. I am making a second sheet that has statistics for the users. So for each user it will count the number of passes, fails and non-completes. My question is how do I write a function to do this? My guess is its something like countif(sheet1!a1:aN, AND(a[i][owner]=name, a[i][pass/fail]=pass). Obviously though this syntax doesnt work in excel, so I would like to figure out how to do it
94850
I want to change color of the list item row based on the field value without any code.  For Example : I do have custom list with fields Title, Status(Pending,Running,Completed). I want to color list items based on color like If Status is    Pending than row color should be yellow Running than row color should be light green  Completed than row color should be dark green.   Can somebody please let me know the easy step to do that without code using just SharePoint designer or any other out of the box solution?  Thanks in advance
67443
In a Google Spreadsheet called Attendance there is a sheet called Template. The user duplicates this sheet, renames the sheet with current date and uses this sheet to mark attendance for students. The Template sheet contains protected cells and the attendance is marked by entering Student's ID number in the space given (unprotected cells). I use the following script to duplicate multiple sheets and rename them everyday:  function createDailyAttendance() {   var ss = SpreadsheetApp.getActiveSpreadsheet();   var refss = ss.getSheetByName("DataPointers");      // Get the range Row and Column information.   var dataRangeRow = refss.getRange("K2").getValue();   //var dataRangeCol = ss.getRangeByName(ColName).getValue();      // Get the range of cells that store Duplicate sheet name.   var AttendanceDataRange = refss.getRange(dataRangeRow);    var AttendanceObjects = AttendanceDataRange.getValues();    var template = ss.getSheetByName('Template');    for (var i=0; i &lt; AttendanceObjects.length; i++) {       // Put the sheet you want to create in a variable      var sheet = ss.getSheetByName(AttendanceObjects[i]);        // Check if the sheet you want to create already exists. If so,       // log this and loop back. If not, create the new sheet.         if (sheet) {            Logger.log("Sheet " + AttendanceObjects[i] + "already exists");         } else {            template.copyTo(ss).setName(AttendanceObjects[i]);            }         }   return; }   This script helps me create multiple copies of sheets from Template but the duplicate copies do not retain the Cell/Range permissions. Is there a way to add a loop function which extracts permission from Template and applies it every time the loop template.copyTo creates a sheet?
972
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.
121870
I'm using a Query in Google Sheets to pull data from another sheet. That query looks something like this:  =QUERY('Raw Data'!A2:AB,"SELECT E, A, C, D, Y, R, S WHERE S = 'New York'",1 )   What I'd like to do, though, is build a custom sort order within the function so that the Query automatically sorts by rows that have "string1" in column E, followed by those that have "string2" in column E, etc etc. The desired order is not alphabetical so I can't use a clause to sort in ascending or descending order.  I'd also prefer to do this without having to create another sheet where I rank my sort order in a table.