FAQ Database Discussion Community

Pull data from multiple columns into one column

join,google-spreadsheet,spreadsheet
I have found many useful answers before, but this is my first question on this forum. Feel free to give me feedback about my question as well as an answer. Below is a link to a Google Spreadsheet in which students signed up for workshops for our career fair. I'm...

MATCH() with search_type 1 doesn't work with certain numbers

function,google-spreadsheet,spreadsheet,google-docs
I've read several similar questions but I couldn't find the answer. I've certain number values that the MATCH() function, with the search_type option set as 1, can't retrieve. The best way to explain it, is to share the spreadsheet (here) where you can see in details on which columns the...

SumIf certain values are in a range

excel,excel-formula,spreadsheet,sumifs
I want to sum up all values that go for a certain country. I tried: =SUMIF(B2:B7;D2=$A$2:$A$7) However, I get 0 back as seen in the picture below. Any recommendations what I am doing wrong?...

How do I write a python macro in libreoffice calc to send and receive data

python,macros,spreadsheet,libreoffice,uno
The premise: I am working in libreoffice calc and need to send an instruction to another program that I know to be listening on a TCP port, via a macro. I am expecting a reply from the listening program and want to insert the reply data into the libreoffice spreadsheet.

How to scale the values in a column based on values in other column in google spreadsheet?

excel,google-spreadsheet,excel-formula,spreadsheet
I want to scale the value of column A and write it in column C based on it's corresponding value in column B and scaling values are given in column F. ...

Randomly generate 2 dates per week over 2 years

excel,google-spreadsheet,excel-formula,formula,spreadsheet
How do I write an excel formula, which randomly picks 2 dates in each week over a 2 year period, where: The 2 dates per week are not the same as each other So this should not happen: 05/02/2015 05/02/2015 The first date in any given week is always before...

Auto remove, if data already exist on other column

excel,spreadsheet,autofill
I made a sample records of students showing if they're able to graduate based on the exam taken on Sheet 1, I just put 1 to mean that they passed that exam and blank if they not. To be under For Consideration you need to pass the Prelim and Midterm...

Call VBA function that returns custom type from spreadsheet

excel,vba,excel-vba,spreadsheet
I have a vba function that returns a custom data type, defined as: Public Type stockValue stock As String value As Double End Type My question is how do I deal with this when i call the function from a spreadsheet cell ? for example, say i want the cell...

Compare two columns from two sheets contains different names of peoples and return numeric value on third

excel,excel-vba,excel-formula,spreadsheet,multiple-columns
I want to compare "Sheet 2" "Column A" (which contains the name of 300 people) with "Sheet 1" "Column A" (which contains the name of 28 people). Now I want write down a formula on "Sheet 2" "Column B" to see whether those 28 people (Sheet 1, Column A) do...

Error when running Wijmo SpreadJS through ExtJS

extjs,spreadsheet,wijmo
I have written a script which creates an Excel-like spreadsheet using Wijmo library (spreadJS). I try to integrate this spreadsheet inside an ExtJS panel but I get an error: Uncaught TypeError: undefined is not a function I have two files. The first is the index.html where I have all the...

Overcome Missing Google Sheets Functions in Excel

excel,google-spreadsheet,spreadsheet
Trying to convert or recreate this Google Sheets formula in Excel. What I have is a list of users that may or may not repeat and I am trying to find the number of purchaes, lowest purchase amount, the total price of purchases and the average price of purchases. Here...

Parsing specific text from cell

regex,google-spreadsheet,spreadsheet
I am having in a google spreadsheet multiple cells the following text(only the Language changes): Lorem ipsum dolor. sit.com<br><br>Languages: English to Dutch<br/><br/> I would only like to display English to Dutch in a separate cell. I tried this: =REGEXEXTRACT(D2,"<br>(.*?)</br>") However, I do not come very far with this solution, because...

How do RDBM’s make working with data more effective than flat files and spreadsheets. [closed]

data,spreadsheet,rdbms,flat-file
Could anyone help me answer this question: How do RDBM’s make working with data more effective than flat files and spreadsheets. ...

Google Sheets Copy Down Issue

google-spreadsheet,spreadsheet,google-spreadsheet-api
I have a google form that my students fill out daily. All form responses feed into one workbook. From the workbook, I have a spreadsheet for "Clean" data. Which is just the information from the responses sheet needed to graph progress. The formula I am using in A2 is: ='Form...

Excel if function failing

excel,function,math,excel-formula,spreadsheet
I am trying to do the following: "Put a formula in D13 that will either Add Number 1, Number 2 and Number 3 or multiply them depending on what you put in D12. You should put in some sort of error message too if the user puts in something incorrect."...

Appyling one formula to excel table

excel,excel-vba,excel-formula,excel-2010,spreadsheet
I have the following table: I would like to calculate the interest rate that I would get for all combinations in this table. The formula I use is for example: =$C$2 + $C$2*B3 Please note, that this is just an example. My real table is much larger and doing it...

Multiplying column values in Google Spreadsheet with ability to add/delete rows

google-spreadsheet,spreadsheet,worksheet-function
I have an invoice, where there are two columns: Rate (Column H) and Quantity (Column I). And there is Amount (Column J). I need a formula so that it would automatically multiply Rate and Quantity and show it in Amount. The number of rows would be changing (added or deleted),...

DevExpress SpreadSheetControl Chart set line color /style

vb.net,devexpress,spreadsheet
DevExpress website is unable to tell me. How can I influence the line color, thickness, etc. of created graphs? Dim chart As Chart = myworb.Worksheets(1).Charts.Add(Charts.ChartType.ScatterLine) chart.TopLeftCell = myworb.Worksheets(1).Cells("A83") chart.BottomRightCell = myworb.Worksheets(1).Cells("F112") chart.PlotArea.Fill.SetNoFill() chart.Series.Add(myworb.Worksheets(1)("A1:A71"), myworb.Worksheets(1)("B1:B71")) chart.Series.Add(myworb.Worksheets(1)("D1:D81"), myworb.Worksheets(1)("E1:E81"))...

How can I filter my search in a column on google sheet which collects data from a form?

google-spreadsheet,spreadsheet,google-docs
I would like to perform a multi criteria search of data in a column- contains data of check boxes(more than one option chosen). For a clearer picture of what I am trying to do, screenshot below is a question in a form Data from the form are saved in sheets...

Adding iterated array values to xlwt sheet

python,arrays,for-loop,spreadsheet,xlwt
For every 4 values in this array, I want it to write to a new row in the spreadsheet while disregarding the first column. This is because I'm going to be putting different values from a separate array there. For example, I have five columns: A B C D E...

Copy columns and paste values (not formulas)

excel,vba,spreadsheet
AlexP provided the following code to a question about copying columns. It works great for me except that in ws1, the columns have equations that get copied over to ws2. I just want to copy over the values, not the equations. Sub CopyHeaders() Dim header As Range, headers As Range...

Calculate sum for a date range in Excel/Google Spreadsheets

excel,google-spreadsheet,excel-formula,spreadsheet,openoffice-calc
I'd like to calculate the sum of multiple values for a range of overlapping dates. It should return the sum for a specific year and split the values that fall into more than one year proportionally. Example: ╔══════════╦════════════╦════════════╗ ║ Value ║ From ║ To ║ ╠══════════╬════════════╬════════════╣ ║ 100,00 € ║...

Sum multiple values in certain rows in a unique formula

excel,google-spreadsheet,spreadsheet
I want to do a formula that multiplies 2 values on each row with the same ID (another value) of the row where the formula is. Like a sumproduct selecting only certain rows (by ID) in the range. I shared the doc here so that you can understand what I...

How to show multiple Date/Times per location?

google-spreadsheet,spreadsheet,google-spreadsheet-api
Using Google Spreadsheets, I need to enter data structured like the example below. There will be multiple "quadrants" Each "quadrant" can contain one or many "days", Each "day" can contain one or many "times". This data will ultimately be imported in some backend db (e.g. Access DB, SQL, MySQL)....

Search Google Spreadsheet for same value and append in next column in GAS

google-apps-script,append,spreadsheet,value
I've got a Google Spreadsheet which looks like this: ID | winkelName | 26/05/2015 | 1 | Foo | | 2 | Bar | | 3 | Foo2 | | 4 | Bar2 | | I'm trying to read and write an array of objects with the following structure: history[i].storeName...

How to implement rangeProtect() over multiple tabs - google spreadsheet

function,google-apps-script,google-spreadsheet,spreadsheet
I'd like to implement a script to implement range protections on the different tabs of the spreadsheet when someone create a new spreadsheet by copying my template. The issue I encounter is when the script run through the protection implementation. My code seems to be fine as per the debug...

Use unique user number in Google Forms

forms,url,unique,spreadsheet
I want to send my clients in my database an e-mail with a request to fill in a Google form to research their needs for our service in the future. Every client has an unique ID already (for example EX0258, EA1405, EZ2815). I need a way how I can add...

In Perl, how can I copy a subset of columns from an XLSX work sheet to another?

excel,perl,spreadsheet
I have a .xlsx file (only one sheet) with 15 columns. I want to read some specific columns, let's say columns 3, 5, 11, 14 and write it to a new Excel sheet. In this case some cells of input files are empty means don't have any value. Here what...

Spreadsheet if else sum

excel,if-statement,spreadsheet,calc
I would appreciate some help writing a statement in a spreadsheet (Libreoffice). IF E4 contains "bought" then do the sum of I4-F4 ELSE / ELSEIF E4 contains "sold" do the sum of F4-I4. I have it partially working: if E4 contains "bought" the correct sum is displayed, but if E4...

How can I sort the output from a query that includes an array formula?

google-spreadsheet,spreadsheet
I have a formula that queries two sheets in the document and pulls data to a third sheet. If I add new data to the first sheet after the query has completed and then go back to the third sheet (with the query results) the new data is not at...

How to make an invisible character in HTML, that still get's picked up on text selection?

html,css,character,spreadsheet
Backstory So I am doing a webchat as a side-project, and one of the issues I am encountering is making the UX of copying and pasting messages friendly. Right now all of the elements are organized within their own tags, spaced out and separated with CSS rules, in a way...

Extract matching data from varying number of row

google-spreadsheet,spreadsheet,pivot-table
I am trying to build graphic indicators from a (potentially) large set of data using google-spreadsheet. So far, I've used a pivot table to extract the information from the raw data, and I want to build intermediate tables to calculate the different values I need for my indicators. So far,...

A script to copy one sheet to various spreadsheets in a specific folder in Google Drive

google-apps-script,spreadsheet
I have a sheet which I need to copy to hundreds of spreadsheets in a given folder in Google Drive. I am just beginning with script and I do not know how to proceed because the script below just gives me an error. Thanks. function myFunction() { var source =...

How to do a sum of a column if a particular cell of each row got a particular value, in a Google Spreadsheet?

function,google-spreadsheet,spreadsheet,google-spreadsheet-api,sheet
I'm trying to make a budget sheet for a trip. I got 2 spreadsheets. 1 contains the steps of the trip. The other contains a dashboard with the sum for each of these 5 groups of steps : "food", "activities", "shopping", "hotels" and "vehicle". My first sheet is like that...

Cannot read property “length” from undefined. (line 39, file “Code”)

google-apps-script,google-spreadsheet,spreadsheet,google-spreadsheet-api
I have written a google Apps Script which is used to filter the data and copy to another sheet. But, Yesterday it was working fine when I open today morning it was showing the error " Cannot read property length from Undefined " onEdt is a user defined function. Don't...

How can I get keyboard events in Google Spreadsheets?

google-apps-script,google-spreadsheet,spreadsheet
How can I get keyboard events in Google Spreadsheets? I want to use this events in my script to format non-adjacent cells.

Google drive spreadsheet and calendar invite guest

google-apps-script,spreadsheet
Okay, first of all, what I'm trying to do is updating Google sheet and calendar simultaneously. So, let say if there's an update to your spreadsheet there will also be an update to the calendar. However, here's all the things that I failed to do. If there's changes in the...

Regex - Link formatting

regex,excel,excel-2010,spreadsheet
I have the following link in excel: http://en.wikipedia.org/wiki/Melon_Bicycles I would like to retrieve the link as: http://en.wikipedia.org In google doc I used: =REGEXEXTRACT(A1; "https?:\/\/(.[^\/]+)"), whereas A1 is the cell with the unformated link. Any recommendation how to implement that in excel 2010?...

Can we put hash tables inside a hash table?

java,hashtable,spreadsheet
Can we link another hash table for each key, in a hash table? My target is to have a very fast 2-d data structure to store cells in a spreadsheet. I'll store "all columns within a row" in a hash table. Then do this to all 'r' number of rows....

Google Sheets - How to create add and subtract buttons to track inventory

button,google-spreadsheet,spreadsheet
I've been searching for similar solutions out there but am coming up short so far. Here is what I want to accomplish: I need to come up with a basic solution to sync inventory quantities at the end of each day. We take physical counts of inventory sold throughout the...

Get null values in Ruby Spreadsheet gem

ruby,excel,spreadsheet
I'm trying to take an excel file and put it into Postgres. I can access the file and read rows from it. require 'spreadsheet' Spreadsheet.client_encoding = 'UTF-8' book = Spreadsheet.open 'C:\test\RubyTestFile.xls' sheet1 = book.worksheet 0 $test_array = [] sheet1.each do |row| $test_array += row end print $test_array My problem is...

Ruby gem spreadsheet returning dates as doubles

ruby,excel,postgresql,gem,spreadsheet
I have a method that runs through an excel spreadsheet and puts those values into an array then into PostgreSQL. Everything runs fine but the cell values that hold dates are being returned as doubles. How can I get the date values? Also what if I don't know when a...

Sheet to Sheet Data Transfer with one constant in each sheet

excel,spreadsheet
I have a regional store manager that needs help in Excel. We will be comparing regional ranks within the national ranks. There are two sheets in the workbook. Sheet 1 columns A-C: RANK | National Store IDs | SALES (largest to smallest) Sheet 2 columns A-E: Regional Store IDs |...

Google analytics ADD-ON google spreadsheets : AVG Session duration and Pagesviews/sessions

analytics,spreadsheet,google-analytics-api,dashboard
Im working on a custom google spreadsheet analytics dashboard. Here's the situation, I'm trying to get specific info: Sessions, users, pageviews pageviews/sessions, avg session duration, bouncerate and % new sessions. The thing is I almost have all the right information except for AVG Session duration and Pageviews/sessions. Here's a screenshot...

how can I use iferror inside of an array formula in google spreadsheets

google-spreadsheet,spreadsheet
So I am working on the same spreadsheet I have been working on for a bit now and it mostly works aside from one small quirk. If any of the queries in an array formula do not have actual data to query in the range they are hitting they return...

How do I write a python macro in libreoffice calc to cope with merged cells when inserting external data

python,macros,spreadsheet,libreoffice,uno
The premise: I am working in libreoffice calc and need to send an instruction to another program that I know to be listening on a TCP port, via a python macro. I am expecting a list of invoice line data from the listening program and want to insert the lines...

tags inside
using importXML Xpath query, in Google Spreadsheet

html,xpath,spreadsheet
I'm using Xpath in Google docs to get the text inside <div>. I want to save the text inside <div id="job_description"> in one cell of Google doc spreadsheet, but it shows each <div> in separate cell. <div id="job_description"> <div> <strong> Basic Purpose: </strong> <br></br> </div> <div> Work closely with developers,...

Get subrange in Google Spreadsheet

google-spreadsheet,range,spreadsheet
I'm trying to compute the average of values in a range, but not including the lowest value. What I'd like to do is something like: =AVERAGE(SUBRANGE(SORT(A1:A10,1,FALSE), 1, 9)) Essentially, this would sort descending, then take the subrange consisting of all but the last element in the now-sorted range, and average...

Excel - Pulling data from one cell within a list

excel,powerpoint,spreadsheet
I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

Google Spreadsheet- Based on text in different cells, change another cell text

excel,google-spreadsheet,spreadsheet
please help me to create a simple Google spreadsheet. 1st function If value/text in cells C3, D3 & E3 ="passed"- change F3's value to "passed" else show "failed" 2nd function If value/text in any of the cell is "passed"- change F3's value to "passed" else show "failed"...

python / gspread - How to update a range of cells with a data list?

python,spreadsheet,gspread
I have a data list (extracted from CSV) and I'm trying to use Python / GSpread to update a range of cells on a Google Doc. Here is my code sample: import gspread def write_spreadsheet(spreadsheet_name, sheet_id, data, start_row): gc = gspread.login('[email protected]', 'password') wks = gc.open(spreadsheet_name).get_worksheet(sheet_index) start_letter = 'A' end_letter =...

Occurences of a value, based on corresponding value across columns