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.

The data table is the "Form Responses 2" tab as the info feeds in from a form.

I'm looking for a formula to put in the cells 'Advocacy Engagement'!P3:P32. The results of the formula should display the number of advocates that have an engagement level of "3" ('Form Responses 2'!I2:I105) for the legislator that same row. This must be done without counting duplicate entries of the same Advocate by Legislator ('Form Responses 2'!D2:G105). In other words, these advocates could be entered multiple times ex. Bob, CEO of K Bank may be entered on the form 2 or more times. This might be because he is advocating with more than one legislator, or he is entered in mulptiple times because his engagement level went up with a particular legislator, or simply multiple notes associated to him has been entered. I only want him to be counted once for that legislator in that row and only if he has reached a level 3 engagement level. The result should be a count of the number of advocates that meet this criteria for that legislator.

As an aide to the formulas on the "Advocacy Engagement" tab, I used column A to show the full name of the Legislator so it matches the legislator name in 'Form Responses 2'!D2:D105.

Also, know that this can not be a count of the "3"s in 'Advocacy Engagement'!J3:O32 as those formulas show the highest level of engagement for that Advocacy Type (column header) regardless how many advocates there are.

I don't know of any other links to the same or similar question like mine. I searched before I posted my question.

Hopefully this answers your questions and you can help me with this. Please let me know if you have any other questions.

I know I can get a rolling 30-day view of data using query and google clock in this manner. Can I not use it in this format for filtering data? Related, how do I intelligently filter by month only - also based on Google Clock. For instance, always show 1 month back in relation to current time? I see many examples for calculating static date ranges in this forum; I have not encountered any using filter.

I'm trying to find a way to return the column header for a row matching multiple criteria in Google Spreadsheet. I've been able to successfully use INDEX MATCH to return the header for a single variable, but when I add in the second one, I can't find how to get it to return the column header for the second variable only.

In the mockup included above, A2:A4 contains criteria 1, while B2:F4 contains criteria 2. I need to return the column header(s) for the row that meets both criteria at the intersection(s) of the column(s) crossing criteria 2. I've tried various attempts with INDEX MATCH and QUERY (of which I'm not great) and haven't been able to get anything. My preference is to keep this to FORMULAE, but any input would help.

Any thoughts? Eventually the final dataset will include multiple worksheets using indirect references, but I just need to get this first component functional

I think this is pushing right to the very edge of what is possible to do in google docs (without scripts), I'm about to write code to do this but thought it might present a great ultimate challenge for the formula pros :)

A1: Title

A2: Snow White and the Seven Dwarves

A3: Beauty and the Beast

A4: Snowey snowtrack the snowmobile

A5: Snow Beasts and Dwarves

A6: A Snowy Christmas

B1: Find Words

B2: Snow

B3: And

C1: Result Sentences

C2: Snow White and the Seven Dwarves

C3: Beauty and the Beast

C4: Snow Beasts and Dwarves

So essentially the function would find all the sentences in Col A that contain any of the words in Col B and spit them out in Col C. It finds sentences that contains words for kids learning english, so if we know a kid is struggling with a few words we can find really good sentences to challenge them with.

I was thinking that before doing a comparison you could add a " " to the end of the words, then add a " " to the end of the Sentence Col as well so it doesn't misfire on "Snowy" and the like but the array function feature is mystifying me. The closest I got was this

=SORT(UNIQUE(FILTER(B2:B;COUNTIF(UPPER(A2:A);UPPER(B2:B))>0)))

But it can only compare words to words, it'd be like that but with a search/find feature.

Every week, the value of the 1RM variable will go up. It'd be nice to be able to simply set a variable so that x=200 so that next week all I have to do is change the variable so that x=205 or x=210 so I don't have to edit each cell manually. Is there a way to do this?-------------------------------------------------------------------------------------------------------------------------------------------------

I think this is pushing right to the very edge of what is possible to do in google docs (without scripts), I'm about to write code to do this but thought it might present a great ultimate challenge for the formula pros :)

A1: Title

A2: Snow White and the Seven Dwarves

A3: Beauty and the Beast

A4: Snowey snowtrack the snowmobile

A5: Snow Beasts and Dwarves

A6: A Snowy Christmas

B1: Find Words

B2: Snow

B3: And

C1: Result Sentences

C2: Snow White and the Seven Dwarves

C3: Beauty and the Beast

C4: Snow Beasts and Dwarves

So essentially the function would find all the sentences in Col A that contain any of the words in Col B and spit them out in Col C. It finds sentences that contains words for kids learning english, so if we know a kid is struggling with a few words we can find really good sentences to challenge them with.

I was thinking that before doing a comparison you could add a " " to the end of the words, then add a " " to the end of the Sentence Col as well so it doesn't misfire on "Snowy" and the like but the array function feature is mystifying me. The closest I got was this

=SORT(UNIQUE(FILTER(B2:B;COUNTIF(UPPER(A2:A);UPPER(B2:B))>0)))

But it can only compare words to words, it'd be like that but with a search/find feature.

In the above sheet, I had a simple function in column C that compared the changes between column D & E. it was =(D2-E2)/D2

Each day I add a column left of D and insert today's data. Afterwards, I have to adjust column C to find the percentage change between column D & E again (because it adjusted D2 & E2 to E2 & F2 when i add a column left of D).

So today I found the INDIRECT function that wont adjust to E & F when I add a column left of D each day. It works in the first Row when I add quotes around the cell reference, but I cant copy it down to the next row.

by the way I cropped the sheet to only a few rows, the original has over 550 rows.

Hi all. I understand the basic concept of an "if then" statement. I'm having an issue with multiple conditions. I have column A that states any number of 1 through 21. In column B I want it to automatically write a percentage:

21 = .1

20 = .09

19 = .08

18 = .07

17 = .06

16 = .05

15 = .04

14 and under = 0

So if A says 21 B would say .1. If A says 13 B would say 0. If A says 18 B would say .07.

Does anyone have an idea how to do this? Hopefully I am writing this clearly.

it works like a charm, but I also need data from other columns from the same row,and I can't use the same code by simply changing the "A:A" because cells in row B, C and D can be null

That's my first problem, the second one is:I need to return a populated cell before the last one BUT it has to be different than the one that I get from the code above, and also the rows adjacent to it.

I'm sorry if this has been posted before, I tried figuring it out on my own, but I just hit a wall.Thanks in advance for any help.---There you go:

I've seen some alternative solutions instead of using MAX posted by Adam & Yogi with explanations that MAX doesn't work so well in ArrayFormula; so I assume since MIN & MAX are best friends that MIN doesn't get along well with ARRAYFORMULA either.

Although just having the correct formula would be AWESOME. It sure would be nice to have the sections of the formula explained so that I can figure it out on my own for future needs.

Thanks Everyone...I sure do appreciate this forum, I use it regularly!