With almost universal availability of Internet and the availability of reliable on-line productivity tools, such as Google Docs, individuals as well as companies are switching over to Cloud Computing. In this blog I will post items of interest to my colleagues/patrons/clients.
Need to pull data from one sheet into another but also filter it
Hi, I am new to the world of spread sheets and am trying to setup some sheets to auto fill off the main sheet.
What I have done is created a master sheet with different categories (venue, address, phone number, day of special etc.)
I would like to be able to pull out the data for each given day onto a new sheet. This would have Venue, suburb & specials for the given day on each particular sheet.
Some venues do not contain specials for every day of the week so when they are pulled into the monday sheet I have many venues listed with blank spcials.
I would just like to pull across the relevant venues that have data located in the monday specials column on the master sheet so that when I am on the Monday sheet only venues with specials on Monday are listed.
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/XGGHbo0tmHM;context-place=mydiscussions calculating number of classes - please help
I have individual students enrolling for classes with a minimum of 2 and maximum of 8. As such, 2 or more to 8 is 1 class. 8 is 1 class. 9 is 2 classes of less than 8. 37 students is 4 classes of 8 and 1 class of less than 8. And so on. So I will have a cell for individual students, a cell of classes of 8 and a cell for classes of 2 of more and less than 8. Can someone suggest and efficient way/arrangement of cells and formulas to for me to calculate this?
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/ROQA-rvzxZI;context-place=forum/docs Return name from column based on match in range of data? I have a range of data, with the corresponding names to the left in a column. I want to have an example of data, and pull the corresponding names from the left out no matter where in the data the match to my example was found. Here is an example:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!msg/docs/aAI6LrJ0Q88/NBmAIbqAAgAJ;context-place=topic/docs/8ogMiVvgF2I Help with query and pivot
Well, I tried for over hour and gave up on this. I am new to query formulas and pivots.
I have a tab with the columns: Client, Date, Actual Hours. This is imported from a bunch of raw data from our time tracking software.
I have a 2nd tab with the columns: Client, Target Hours. The data in these 2 columns is manually entered.
I want a 3rd tab to have the columns: Client from tab 2, Target Hours from tab 2, Actual Hours by Month (multiple columns for each month from tab 1), Total of all months. See below and attachment.
Client Target Hours Jan Feb March Apr ..... Total
John Doe 2 .5 1 3 2 30
Jane Doe etc ......
On the attachment its using a pivot table, which is formatting it EXACTLY how I want BUT its not filtering out the clients we've deleted by manually entering them on tab 2. So imagine we have 100 clients but we only care about tracking our time on 50 of them. Thats what the 2nd tab is for, its a manually selected list of clients we care about and we want the 3rd tab, time by month, to only show the 50.
I tried the following query which works for one row, John Doe (A2) but I dont know how to make it continue down the rows by having A2 increment to B2, C2, etc.
=query('RAW Data Time'!A:AA,"select AA, sum(K) where AA ='"&A2&"' group by AA pivot A")
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/8ogMiVvgF2I;context-place=mydiscussions Regex formula to replace multiple first words with multiple substitutes
How do I copy from one sheet to another if cell string begins with an apostrophe.
I want to substitute only at the beginning of a string (followed by a space). For example:
s -> 's
d -> 'd
re -> 're
've -> /ve
or in the complete string:
m going home -> 'm going home
Something like =substitute(A1, "m", char(39)&"m") works for one substitute, but I think regexreplace will be better to replace multiple substrings and only at the beginning of the cell string.
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/PCkUjhZIZPU;context-place=forum/docs How do I find the average of row sums when pulling data via Google Forms?
I am collecting data using Google Forms and retrieving responses in sheets. Because each form response adds a row, I am having trouble with my formula.
I need to find the total numerical score of each row and then find the average of these row totals. I have established a separate sheet to run the formulas, but the combination of pulling data from a separate sheet, totaling across a row, and then averaging several totals is beyond my limited technical abilities.
I have a sheet named "MP1 Responses." I would like to find the total score of each row depending on the number of Google Form responses. Then, I need to find the average of row 2's sum, row 3's sum, row 4's sum, etc. Then, I would like this average score to appear in B2 of my sheet named "Totals."
Based on my reading, it seems that I might need to use an array, but I am not sure how. Any help would be appreciated.