excel,google-spreadsheet,spreadsheet

You can use a Pivot Table to get this result. Go to Insert > Pivot Table. Add the Email to Rows, Qty to Values, and add Price to Values 3 times. Right click each Price column > Summarize Values By, and select the appropriate aggregation. You can change the column...

google-spreadsheet,spreadsheet

In the top column S cell where you want the result, enter the formula =SUM(T1,U1,V1) replacing row number "1" with the row number on which you are entering the formula. Then, copy (ctrl-c) the cell containing the formula, and paste it into the cells in column S below. When you...

google-apps-script,google-spreadsheet,spreadsheet

keyboard events for cells cannot be handled from apps script. you can only detect a cell change once it has beeb commited (user typed and pressed enter for example), which is not a keyboard event (its a range edit event)

google-spreadsheet,spreadsheet

Please try: =sort(ARRAYFORMULA({QUERY(Sheet1!A1:I500, "Select * where G = 'no'", 0); (QUERY(sheet2!A1:I500, "Select * where G = 'no'", 0))}),6,1) ...

It means that @sheet.row(1)[2] is a DateTime object. It doesn't have .date method. Instead, you can use .to_date. But I don't understand why do you want to convert it to Date. What you want is to use .strftime like this: @sheet.row(1)[2].strftime("%-m%-d%y%-k%M") DateTime.new(2014, 6, 14, 8, 23).strftime("%-m%-d%y%-k%M") # => "61414823" ...

excel,google-spreadsheet,excel-formula,spreadsheet

Just use VLOOKUP. In C: =$A1*VLOOKUP($B1, $E$1:$F$3, 2, FALSE) To do all the lines in a single formula: =ARRAYFORMULA($A1:A*VLOOKUP($B1:B, $E$1:$F$3, 2, FALSE)) ...

javascript,google-spreadsheet,spreadsheet,gs-vlookup

@AD:AM I thought that *sign(Row(A2:A)) was required when used inside of an arrayformula for a multi-Column Call. But I just tried it with a brand new sheet and it looks like you're right! And i just tried it out but new sheets still doesn't support Vlookup Multi-Column Calls outside of...

excel,table,excel-formula,metadata,spreadsheet

Well I came up with a solution, however it uses vba not an excel formula, so it may not suit your needs What this function does is look at the column on the left hand side (your master) and read the value out of it. It then looks for the...

The equal sign (=) in front of SUM should not be there. If your formula works I guess Google Docs just ignores it. Also, the function SUM() is useful to add the values from a range of cells from the same row or column when you don't know in advance...

If you have two columns of the shirt numbers and the corresponding player names then vlookup() will do this, but a warning : are shirt numbers unique i.e. one player one number... With a list of numbers in D2 to D8 and corresponding names in E2 to E8, then =VLOOKUP(A2,D2:E8,2,0)...

google-apps-script,spreadsheet

For this, if you want to create a new event for each new entry in spread sheet you could use Calender class and create an event. If you want to edit an already created event and add new guests you could use CalendarEvent class and add guests. Hope that helps!...

excel,google-spreadsheet,spreadsheet

Adding a criteria for column E should be sufficient to cancel out the non-matching rows. In B6 use, =SUMPRODUCT(($E$6:$E$13=E6)*($F$6:$F$13)*($G$6:$G$13)) Fill down as necessary....

Here's a slightly more Ruby interpretation: require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' def read_spreadsheet(path) book = Spreadsheet.open(path) sheet1 = book.worksheet 0 test_array = [ ] sheet1.each do |row| test_array << (row + [ nil ] * 3).first(3) end test_array end puts read_spreadsheet('C:\test\RubyTestFile.xls').inspect If you'd rather have literal 'null' in there, you...

google-apps-script,append,spreadsheet,value

In a Google spreadsheet you can write a range of cells at once. To do so, create a multi dimensional array with one column and for each row another dimension and within each row a dimension for each cell you want to write. Loop through your checkings, set the cell...

google-spreadsheet,spreadsheet,google-docs

Let's say you have your form in the response sheet in columns A to P, with the multiple choice in col D. If you want to filter your data on the word 'Commercial' you can either do: =filter(A2:P, regexmatch(A2:P, "Commercial")) or use query(): =query(A2:P, "select * where B contains 'Commercial'...

excel,excel-formula,spreadsheet

Excel does not handle error values in calculations very well. One approach might be to check each value for an error before using it E.g. in E2 use =IFERROR(A2,0) *IFERROR(B2,0) + IFERROR(C2,0)*IFERROR(D2,0) Copy to E4 ...

join,google-spreadsheet,spreadsheet

In addition to previous answer, also try: =ArrayFormula(transpose(split(concatenate(query('Form Responses'!D:O, "select D, F, H, J where O='Architect'")&char(10)),char(10)))) This formula should output 1 column. See if it works for you ?...

html,css,character,spreadsheet

As all of the elements are organized within their own tags, you could use :after with the character that you want as separator in the content (with font-size:0). As far as I know, this solution will only work on IE, as the other browsers don't select the pseudo-elements): span:after {...

google-spreadsheet,spreadsheet,worksheet-function,formulas

If you had your base date in A1, the following formula would be good for the first day of the previous month: =date(year(A1),month(A1)-1,1) It's even simpler to find the last day of the previous month: =eomonth(A1,-1) ...

excel,function,math,excel-formula,spreadsheet

I think you had a problem with setting the brackets and in output of "error message". Does this formula work? =IF(D12="Add",SUM(D9,D10,D11),IF(D12="Multiply",D9*D10*D11,"ERROR")) ...

javascript,node.js,web-applications,spreadsheet

node-xlsx is the library you're looking for: https://www.npmjs.org/package/node-xlsx NB: If you need to parse in front-end, you may use Browserify to convert the library to an embeddable script....

If you have the id ready to go from your db you can do it via Google Forms, but it seems the process cannot be automated (see https://support.google.com/docs/answer/160000?hl=en) If you are open to a 3rd party solution, you could do this with Cloud Snippets via cloudward.com. You could just include...

sorting,automation,google-spreadsheet,spreadsheet

Try 'fixing' the first row in the 'view' menu (freely translating). That should prevent the header row from shifting when you sort the column.

excel,google-apps-script,spreadsheet,google-apps,google-spreadsheet-api

You can not bind some kind of extra information to a spreadsheet cell in a Google spreadsheet. So, answer to question one is, "no". Being able to do that would probably need to be done at the Google spreadsheet development level. So, it would be like a feature request. I...

On sheet 2 use this formula in cell D2 to get the sales. =INDEX(Sheet1!C:C,MATCH(Sheet2!A2,Sheet1!B:B,0)) In E2 use this to get the rank =INDEX(Sheet1!A:A,MATCH(Sheet2!A2,Sheet1!B:B,0)) Copy down. ...

data,spreadsheet,rdbms,flat-file

They are handling data in fixed-size blocks and indexing.

excel,vba,ms-access,spreadsheet

Sort on Product within Name and in D2 (?): =IF(A1=A2,D1&", "&C2,C2) in E2: =A2<>A3. Copy both down, select all, Copy, Paste Special, Values over the top, filter to select FALSE for ColumnE and delete all visible....

date,google-spreadsheet,spreadsheet,gs-conditional-formatting

I would not recommend that many different colours (if only because each colour after the first needs a separate rule!) and don't really see the point of D2 for the CF - unless you plan to have a separate set of 12 colours for each year? However, I think what...

excel,google-spreadsheet,spreadsheet

Try IFERROR(AVERAGEIFS(A2:A19,D2:D19,'Food',A2:A19,"<>"),0) This will average everything in Column 'A' where Column 'D' is 'Food' and the value in Column 'A' is not blank. If you're trying to get an average over all months even if you didn't spend anything in a given month, though, you might want to use '0'...

excel,excel-vba,excel-formula,spreadsheet,multiple-columns

Try this: =IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$A$28,1,FALSE)),8,2) Put it in cell B1 in Sheet2 and copy and fill down....

A Double nested for loop; one for the rows, one for the columns, then update each cell individually. This section seemed to work properly with the desired outcome: try: idx = 0 for (start_row, rowlist) in enumerate(data): for (colnum, value) in enumerate(rowlist): cell_list[idx].value = value idx += 1 if idx...

csv,cors,spreadsheet,jekyll,google-docs

Getting datas from google docs is becoming harder ;-( I've tried with jquery.ajax but I met the CORS limitation. Then I found tabletop and it works ! go to your google spreadsheet and File > Publish to the web > Start publishing note the publish url download tabletop script and...

google-spreadsheet,formula,spreadsheet

Maybe you can also consider the use of AVERAGEIF() as it seems the function is built for situations like this: =averageif(A2:A; "moderate"; B2:B) ...

c#,asp.net,excel,openxml,spreadsheet

You cannot define styles like that . You need to create a Style with Fonts , Fills , Borders and create cellformats from defined Fonts , Fills , Borders as given below. // <Fonts> Font font0 = new Font(); // Default font Font font1 = new Font(); // Bold font...

spreadsheet,vlookup,openoffice-calc

You should use VLOOKUP() for this: =VLOOKUP(B2;Sheet2.A2:B6;2;0) VLOOKUP (V = Vertical) is a function that will do exactly as you described, the parameters are: What value you wish to find (in this case B2 = 119) Where is the data (in this case the other sheet's A:B range), it is...

spreadsheet,javafx-8,fxml,controlsfx

As you can read here, it seems ControlsFX and Scene Builder don't play well together (yet). All I can managed to integrate was just the SpreadsheetView on a FXML file, more like a placeholder for the control, since we can't add a GridBase object. <?xml version="1.0" encoding="UTF-8"?> <?import org.controlsfx.control.*?> <?import...

button,google-spreadsheet,spreadsheet

You can use Google Apps Script for this. If you are unfamiliar, in your particular spreadsheet, go to Tools → Script Editor and then select the Blank Project option. Then you can write functions like this to achieve what you want! function subtractSold() { var sheet = SpreadsheetApp.getActiveSheet(); var c1...

excel,excel-formula,spreadsheet

You would use conditional formatting. To get the result name to print, use this formula: =INDEX($A$2:$A$6,MATCH($E2,$B$2:$B$6,1)) For the Number: Reproduce these steps: Change your table so the values aren't equal to each other, as in, the Perfect(Max) shouldn't be the same as the perfect-(Min). Take the result cell, and go...

Try this formula in B1 copied down =IF(COUNTIF(Surnames,REPLACE(A1,1,FIND(" ",A1),"")),REPLACE(A1,1,FIND(" ",A1),""),"") The REPLACE/FIND part extracts the surname from A1 (assuming A1 is in the form <firstname><space><surname>) and then the formula uses COUNTIF to count that surname in your named range. If it's there the surname is returned, otherwise you get a...

excel,google-spreadsheet,spreadsheet

For the first function, you would need to enter the following in cell F3: =IF(AND(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether C3, D3 and E3 contain "passed" and if so returns "passed". For the second function, this is very similar: =IF(OR(C3="passed",D3="passed",E3="passed"),"passed","failed") This checks whether any of the three cells contain "passed", and if...

function,google-apps-script,google-spreadsheet,spreadsheet

Answering my question, The main script discribs the way to find the sheet and the range(s) to protect / unprotect, for different users using the remove/addEditors(mail) function //main script - where to protect - who can edit function Protect_UnProtect( myrange, mysheet, mydescription, startRow, colnumber, numRows, columncount) { var ss =...

google-spreadsheet,spreadsheet,google-spreadsheet-api

As JPV commented, the "pushing down of formulae" by form submissions may be counteracted by using an array formula (in row 2): =ArrayFormula('Form Responses 1'!N2:N) And as Akshin commented, if you were in fact bringing over a couple of columns for graphing purposes, you can do it all in one...

You can set the color and thickness by using ShapeFormat.Outline property of your series objects. Call to ShapeOutlineFill.SetSolidFill method to set the color of your line and use the ShapeOutline.Width property to set the line width. Here is example: chart.Series(5).Outline.SetSolidFill(Color.Magenta) chart.Series(5).Outline.Width = 20 ...

As you've discovered, DATEVALUE expects a string that looks like a date, not an actual date. However, there is nothing stopping you from taking your dates and converting them to strings with TEXT. The right format mask can distinguish between dates and string dates for you. The formula in...

regex,excel,excel-2010,spreadsheet

A simple approach would be to use LEFT with FIND. Something like this =LEFT(A1,FIND("/",A1,9)) ...

excel,excel-vba,excel-formula,spreadsheet

With data in B1; in C1 enter: =TRIM(MID(SUBSTITUTE($B1,"-",REPT(" ",999)),COLUMNS($A:A)*999-998,999)) and copy across: ...

How about Map<String, Map<Integer,Integer>> asdf = new HashMap<String, Map<Integer, Integer>>(); But to be honest you should start with wrapping it inside an object. In this structure globally will be very inconvenient. You could try this BiHashMap public class BiHashMap<K1, K2, V> { private final Map<K1, Map<K2, V>> mMap; public BiHashMap()...

count,google-spreadsheet,spreadsheet,conditional-statements

With row and column labels set up as required for the output and assuming Quality is in A1, and Firm for the output in G1 then (using functions!) please try: =countifs($B:$B,$F2,$A:$A,G$1)+countifs($C:$C,$F2,$A:$A,G$1)+countifs($D:$D,$F2,$A:$A,G$1) in G2 copied across and down to suit....

function,google-spreadsheet,spreadsheet,google-spreadsheet-api,sheet

Simple SUMIFS: =SUMIFS(Sheet1!C1:C99, Sheet1!D1:D99, A1) Assuming description in second sheet matches description in 4th column (the formula is not going to do the stemming for you to convert Activity to Activities - you can use 1 column for exact match string and 1 column as reporting label...)...

Although it seems as such, you are not actually adding a column to the data. That is just a result of using View(). It is showing the "row.names" attribute of the data frame as the first column, but you really didn't add the column. This is expected and documented behavior....

google-spreadsheet,spreadsheet,pivot-table

I think this is possible using a formula: ={QUERY(A2:D,"select A,count(D) where A <> '' group by A pivot C"),QUERY(A2:D,"select count(D) where A <> ''group by A pivot B")} Here is an example sheet (the formula is in cell F1): https://goo.gl/T9xI1v...

google-spreadsheet,formula,spreadsheet

Formula to be dragged down: =mod(B2-A2;1)-if(mod(B2-A2;1)>time(4;0;0); time(0;30;0); 0) Because of the mod() function this formula should deal with 'negative' hours in case of overnight time. Or in arrayformula: =ArrayFormula(if(len(B2:B); mod(B2:B-A2:A;1) - if(mod(B2:B-A2:A;1)>time(4;0;0); time(0;30;0); 0);)) Format the output column as 'duration'......

designated_rows is holding the entire spreadsheet in memory, now that you have that variable you need to save it down as a new spreadsheet file, or you can do this after you've modified everything: raw_data = Spreadsheet.open '../October 2014.xls' raw_data_sheet = raw_data.worksheet 0 keys = ["N/A", "N/A", 0.0, 0.0, 0.0,...

How about the following code: Sub test() Dim i As Integer Dim inputWS As Worksheet, outputWS As Worksheet Set inputWS = Sheets("input") Set outputWS = Sheets("output") i = 1 For Each cell In inputWS.Range("A2:B374") i = i + 1 outputWS.Cells(i, "A").Value = cell.Value outputWS.Cells(i, "B").Value = inputWS.Cells(1, cell.Column).Value Next End...

It seems like there was an issue in the xml itself, as if someone has explicitly modified it. Fixed it by checking for the color theme value while reading fonts in styles.xml and if not set, set the alpha value of the font to 0xFF and voila, everything works Re-saving...

google-spreadsheet,spreadsheet,worksheet-function,formulas

If you want to use OR in Arrayformula you'll need to use +. Can you see if this works ? =ArrayFormula(IF(LEN(H4:H), IF((O4:O = "Yes")+(H4:H = "Yes"),"Yes","No"),)) ...

google-apps-script,spreadsheet

I suppose you have seen this post where the OP wanted to change the background color of today's date in a sheet, your case is very similar except that - if I understood you well - today is not necessarily present in the sheet. So what we need is to...

Since you mention "a certain character" I take it your x is a literal so would suggest: =SUBSTITUTE(A1,"""x""",""""&B1&"""") in Row1 copied down. That is a similar approach to @xificurC's but would be more flexible for whatever lies on either side of "x"....

Have you never copied a subset of columns from an array of arrays to another? Here is the input sheet I used for this: and, this is what I get in the output file after the code is run: #!/usr/bin/env perl use strict; use warnings; use Excel::Writer::XLSX; use Spreadsheet::ParseXLSX; my...

excel,excel-formula,spreadsheet,sumifs

You need to specify the 'test' range and the 'sum' range as separate parameters: try =SUMIF($A2:$A7; D2; $B2:$B7) ...

python,macros,spreadsheet,libreoffice,uno

Having been helped many times over by searching stackoverflow, I thought that I would post a solution to a problem which took much effort to resolve. The code splits the data into lines and each line into data items delimited by the sending program, by tab. The data is inserted,...

excel,google-spreadsheet,excel-formula,spreadsheet,openoffice-calc

I am suggesting the use of helper columns, with the column headers being the years for which the totals will be calculated i.e. 2015 and 2016. I have added an extra row to the data to make sure that it works when the time period is not a whole year....

Just split this line: Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value)) and use the .PasteSpecial() method instead e.g. Range(header.Offset(1, 0), header.End(xlDown)).Copy Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value)).PasteSpecial(xlPasteValues) Application.CutCopyMode = False ...

google-spreadsheet,range,spreadsheet

We may have several occurrences of the minimum; I suggest (a1+a2+...+a10 - min)/9: =(SUM(A1:A10)-MIN(A1:A10))/(COUNT(A1:A10)-1) ...

excel,google-spreadsheet,excel-formula,formula,spreadsheet

There are 21 different combinations of 2 days in a week - to ensure an even distribution you can use a helper column, so assuming your weeks start at A2 and days at B2 (always showing 1 then 2 in days column for each week) then use this formula in...

excel,google-spreadsheet,spreadsheet

SUMIF with multiple criteria doesn't exist, you should be using SUMIFS. Your setup would be as follows: SUMIFS(sum_range1, criteria_range1, criterion1, criteria_range2, criterion2) Formula edited to reflect comments below....

You are looking for SUMIFS together with a normal SUM: What you wanted: =SUM(SUMIFS(B1:B6, C1:C6,{"Open", "Complete"},D1:D6,{"Jan", "Feb"})) The way this works is as follows: SUMIF([summing range], [range 1], [condition 1], [range 2], [condition 2], ..) Where [summing range] is the range to sum over [range 1] is the range of...

c#,winforms,devexpress,spreadsheet

Your code is extremely slow because SpreadsheetControl is trying to update its content with any change in any cell. So, you just need to lock updates before running your code and unlock its after. For lock/unlock updates you can use SpreadsheetControl.BeginUpdate and SpreadsheetControl.EndUpdate methods. Here is example: spreadsheetControl1.BeginUpdate(); var cells...

google-spreadsheet,spreadsheet,google-spreadsheet-api

I’m not quite sure what answer you are expecting but wanting to post an image (and probably length!) is why this is not a comment. Poor data layout that requires changes to help legibility or changes to facilitate further processing is, IMO, a very big issue – much more so...

formatting,control,conditional,spreadsheet

I managed to find a way to fix my problem For Each c In Me.Spreadsheet1.Range("A8:XFD8") If c.Value = "1" Then c.Interior.Color = RGB(0, 0, 255) c.Font.Color = RGB(0, 0, 255) ElseIf c.Value = "0" Then c.Interior.Color = RGB(255, 0, 0) c.Font.Color = RGB(255, 0, 0) End If Next c basically...

google-spreadsheet,spreadsheet,google-apps

Turns out it's pretty easy. Here's the formula for the first column: =if(B2, B2, A2) ...

I could observe the issue with SpreadJS and have forwarded it to the development team for review.

excel,excel-vba,excel-formula,excel-2010,spreadsheet

you need to put this in cell C3 and fill to the right until the last column and then select the first row and copy/fill down until the last row: =C$2+$B3*C$2 the principle is to lock the row for your main values in the second row by adding a $...

google-apps-script,google-spreadsheet,spreadsheet,google-spreadsheet-api

You should add a check for copyData in the last line: if (copydata.length) { destination.getRange(1,1,copydata.length,copydata[0].length).setValues(copydata); } ...

google-spreadsheet,spreadsheet,formulas

Take a look at this example sheet where I used this formula to generate the desired output =query(ArrayFormula(if(len(query({filter(Sheet1!A2:B, len(Sheet1!B2:B)),filter(ArrayFormula(if(Sheet1!B2:B="yes", "Right", "Wrong")), len(Sheet1!B2:B))}, "select Col1, count(Col2) group by Col1 pivot Col3"))=0, 0, (query({filter(A2:B, len(Sheet1!B2:B)),filter(ArrayFormula(if(Sheet1!B2:B="yes", "Right", "Wrong")), len(Sheet1!B2:B))}, "select Col1, count(Col2) group by Col1 pivot Col3")))), "select Col1, (Col2+Col3)*1, Col2, Col3, (Col2/(Col2+Col3))*100...

python,arrays,for-loop,spreadsheet,xlwt

To solve this I took 2 generators. One generator gives me the next column I should write at and the other generator that gives me the next row I should write at. Generators might sound intimidating but over here they really simplify the problem you are facing (with the advantage...

function,google-spreadsheet,spreadsheet,google-docs

You need to use "0" for the last paramenter because your range is not sorted in ascending order. 1 only works for searching sorted ranged.

ruby,excel,postgresql,gem,spreadsheet

@engineersmnky comment and this post helped me figure it out. How to convert MS excel date from float to date format in Ruby? require 'date' require 'active_support/time' puts (DateTime.new(1899,12,30) + 42153.days).strftime("%Y-%m-%d") puts (Time.at( (42153 - 25569).days ).utc).strftime("%Y-%m-%d") Either of these solutions work. Basically excel stores dates as doubles and you...

I completed a similar project a few years ago here's the approach I took. If possible try and get the spreadsheet to come across as a .csv format as it makes the data easier to manipulate. Worst case scenario, convert it yourself, there's a number of examples online. As for...

google-spreadsheet,spreadsheet

Try combining both ranges (from both sheets) inside 1 query instead of using 2 queries, and wrap an IFERROR() around that single query: =ARRAYFORMULA(IFERROR(QUERY({Sheet1!A1:I500; sheet2!A1:I500}, "Select * where Col7='no'", 0), 0)) See if that works for you ?...

Sure. Assuming that you put the value of the subject in the next column (D from you example), just do something like the following: // This constant is written in column C for rows for which an email // has been sent successfully. var EMAIL_SENT = "EMAIL_SENT"; function sendEmails2() {...

google-spreadsheet,formula,spreadsheet

Please try in C2 and copied down to suit: =if(B2>=percentile(B$2:B$51;0,75);"YES";"NO") I hope your classes are no bigger than 50....

excel,google-spreadsheet,spreadsheet

This function should work in both Excel and Google Sheets: =LEFT(A1,FIND("'", A1)-1)*12+(MID(A1,FIND("'", A1)+1, FIND("""", A1)-FIND("'", A1)-1)) Tested it with 6'2" and 12'11"...

analytics,spreadsheet,google-analytics-api,dashboard

Check the locale in your spreadsheet settings. It needs to be US to match the GA data being sent.

select your data then Home > Conditional Formatting > Data Bars, possibly > More Rules..., adjust to suit

excel,vba,excel-vba,spreadsheet

You can only return data types that Excel understands from a user-defined function. Excel does not understand custom data types. Instead you would have to return a variant array containing the 2 values from your custom data type. Then either you would array enter the function into 2 cells, or...

python,xml,excel,lxml,spreadsheet

If you need to use a namespace for an attribute of an Element or SubElement you can't pass it using the **kwargs (**_extra) syntax, as that does only allow to specify attributes with a name that is a valid python identifier. So in this case you need to use the...

google-spreadsheet,spreadsheet

Try this: =MATCH("*"&$B$2&"*",INDIRECT("Data!"&CHAR(64+row(A2))&":"&CHAR(64+row(A2))),0) CHAR(64+row(A2)) is the same as CHAR(66) which is B, as you drop it down, Char() increases by one so you get the next column. So CHAR(64+row(A3)) is the same as CHAR(67) which is C...

excel,if-statement,spreadsheet,calc

something like: if(exact(e4;"bought");i4-f4;if(exact(e4;"sold");f4-i4)) works for you???...

if-statement,spreadsheet,nested-if,multiple-arguments

This might be what you are looking for, ties the start week/ end week and year together for comparison. =IF(AND( (K$1&TEXT(K$2;"00")) >=(RIGHT($E$3;4)&TEXT($F$3;"00")); (K$1&TEXT(K$2;"00")) <=(RIGHT($G$3;4)&TEXT($H$3;"00"))) ;1;0) ...

google-apps-script,spreadsheet

I was given this answer by someone within a community in Google+ and it works: function copyToSheets() { var source = SpreadsheetApp.getActiveSpreadsheet(); var sheet = source.getSheets()[1]; var folders = DriveApp.getFoldersByName('ID'); var firstFolder = folders.next(); var folderFiles = firstFolder.getFiles(); var thisFile; while (folderFiles.hasNext()) { thisFile = folderFiles.next(); var currentSS = SpreadsheetApp.openById(thisFile.getId());...

You just need to add an AND condition that excludes the values Passed in Sheet1's column F. =IFERROR(INDEX(Sheet1!A$2:A$9999, SMALL(INDEX(ROW($1:$9998)+((Sheet1!E$2:E$9999<>"Passed")+(Sheet1!F$2:F$9999="Passed"))*1E+99, , ), COUNTA(A$1:A1))), "") This method is based on excluding non-matching results so if we want to keep the entries that have Passed in column E, we use +(Sheet1!E$2:E$9999<>"Passed") to exclude...

google-spreadsheet,spreadsheet,worksheet-function

The following ArrayFormula() spans from row 5 ("Books Name") to row 14 ("Total Price"), so adding rows or deleting them should not be a problem: =ArrayFormula(if((C5:C14="Books Name")+(C5:C14="Total Price")=1,"",H5:H14*I5:I14)) However, since the formula will have to reside in the column J, the following formula is even more useful: =ArrayFormula(if(C5:C14="Books Name","Amount",if(C5:C14="Total Price","",H5:H14*I5:I14)))...

regex,google-spreadsheet,spreadsheet

Maybe try =regexextract(D2, ":\s(.*?)<") ...