yogi_Compute Sum Of Hours By Day, Month, And Year As Specified Year

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

AFUSA said:

Need formula to interpret timestamp's from Form, by month, year and by day,month,year
I have a Form that employees submit their time worked each day. The time stamp logs it. I need a formula to add all the hours of this month, from this year, and for this day, this month, this year.
Here is what I was using: =ArrayFormula(if(len(J1),SUMIF(month(A2:A),J1,C2:C),iferror(1/0))
But next year it will include the months from both years.
------------------------------------------------------------------
In the following solution I used the Query function to compute the sum of hours for current day, current month and year, and current month all years

yogi_Paste Value In A Cell Using Data Validation

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
jkm1317 said:
Is there a way to automatically input a formula for ONE TIME only? I have a sheet where I am using vlookup to calculate a total. (Price*quantity = total). Well as time (years) goes on this price has changed (e.g. inflation). So my issue is that when i change the price all my old calculations would change as well. i would like gdocs to lookup the answer and hold it.
I hope you can help!
Thanks!
------------------------------------------------

yogi_Find Information For A Validated Cell From Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

chk4p said:

So let's assume that I have a sheet ("ContactInformation") of contact information that looks like this:
John Smith, email@email.com, 5133471111, etc
Jane White, contact@random.com, 4343141592,etc
Now, in a separate sheet, I have a row (we'll call it the "SelectedUser" row) that begins with a validated cell (drop down), from which I select John Smith:
How can I populate the rest of the SelectedUser row with the information from the "ContactInformation" sheet?
I want this to be done *dynamically*. For instance, specifying a long block of IF's is not useful, because it doesn't actually cut down on much work.
Any tips, tricks, ideas?
--------------------------------------------------------------

yogi_Compute Employee Hours Worked From Time_In Time_Out Clocked Via Google Form

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

jb2301 said:
I'm pulling my hair out trying to figure out how to get a formula to copy itself down when a form posts
I am using a Form as an employee timeclock. The three columns posted are timestamp, employee name, and Clock In/Clock Out. I'm trying to add some simple formulas to the right of the data that gets posted by the form to calculate my daily labor cost per employee. The problem is each time the form posts new data, my formulas aren't copied down to the next line where the new data was posted. I have to manually copy these formulas down each time. I've tried the Expand function and some other Array formulas but it hasn't worked for me. Can someone please help!!
----------------------------------------------------------

yogi_Extract Rows Of Interest From Sheet1 In Another Sheet And Publish Maitaining Security of Sheet1

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

klor000:
I'm sure this is very easy to do, but I just can't seem to figure out how to do it.
I have a spreadsheet that only I can read/edit. I want to extract out those entire rows in which the first character in column J is a 1 (entered as text, not a number) and put all those rows in another spreadsheet that I can then share with people who just need to see those rows of information. Of course, over time, when I change my original private spreadsheet, I want their extracts to be appropriately updated to show whichever rows currently have that 1 in column J.
Thanks a lot for any help. I'm obviously quite new to this.
Karen
-------------------------------------------------------------------
Hi Karen:
In Sheet2 I have extracted the rows of interest per your specification, and then I have published only Sheet2

yogi_Extract Rows Of Interest From Sheet1 In Another Sheet And Publish Maitaining Security of Sheet1

klor000:
I'm sure this is very easy to do, but I just can't seem to figure out how to do it.
I have a spreadsheet that only I can read/edit. I want to extract out those entire rows in which the first character in column J is a 1 (entered as text, not a number) and put all those rows in another spreadsheet that I can then share with people who just need to see those rows of information. Of course, over time, when I change my original private spreadsheet, I want their extracts to be appropriately updated to show whichever rows currently have that 1 in column J.
Thanks a lot for any help. I'm obviously quite new to this.
Karen
-------------------------------------------------------------------
Hi Karen:
In Sheet2 I have extracted the rows of interest per your specification, and then I have published only Sheet2

yogi_Extract Entries That Do Not Match Entries In A Specified Column In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
fin.severven said:
The problem:
I want sheet 3 to have lines from sheet 1, that has only those letters in Sheet1!B:B that DO NOT match with letters in Sheet2!A:A.
I guess it could be some combination of Filter and Unique, but could not find the way to do it.
For example:
sheet 1
A B
1 timestamp letter
2 01/01/00 00:00:01 a
3 01/02/00 00:00:01 b
4 01/03/00 00:00:01 c
5 01/03/00 00:00:02 d
Sheet 2
A
1 a
2 b
3 e
Sheet 3 should be
A B
1 01/03/00 00:00:01 c
2 01/03/00 00:00:02 d
Thanks in advance for any help with that.
---------------------------------------------------------

yogi_Compute Attributes For Specified Dates From Given Sets Of Date Sequences

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

jettechfsr said:

Check colums if a date is between them
Ok see Example sheet I have four Colums of Dates Worked aand Dates OFF
Worked OFF
C3:C4 to D3:D4 and E3:E4 to F3:F4
I need to check A6:A20 If date is equal to or between C3:C4 to D3:D4 = "Worked" in B6:B;20
I need to check A6:A20 If date is equal to or between E3:E4 to F3:F4 = "OFF" in B6:B;20
I need to check A6:A20 if date is not in either colums blank "" B6:B;20
please help have posted this question in one of my other thread no replies sorry for the bump but this is one sheet and easier to follow I hope
https://spreadsheets.google.com/spreadsheet/ccc?key=0Ap7Eq_g0fO3BdC1qZDhLQktaRll0QzhhQW1HYmdROWc&hl=en_US
-----------------------------------------------------------------------------

yogi_Extract The Latest Submittals by Name From Multimple Google Form Submittals

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

caitlinwconnely said:
I have shared my template with your gmail address. Here is some background info:
I am in charge of coordinating all weekend activities at the boarding high school that I work at. Each week I would like to set up a form displaying all of the activities available for that weekend, and let students sign up by typing in their first and last name, then selecting the activities they would like to attend. However, students can change their mind until I close the form - so they can submit multiple entries. That is where your formula comes in. I am hoping to view only the most recent submission based on filtering the First Name and Last Name fields against all of the other cells in those columns (so if both fields match, the spreadsheet would only show the most recent timestamp). There could be as many as 400-500 entries in any given week. I plan on just keeping a template master copy, then using that to create a new form each week.
I hope you can understand what I am trying to accomplish from this. If not, please let me know and I will try to explain more.
Thank you so much!
-Caitlin
-----------------------------------------------------------
I have edited your Form to delineate weekend activities, then I populated your spreadsheet with some sample data (9-10 form submittals) and then I added a sheet named yogi_FinalCopy in which I have extracted the latest submitted data by name.

yogi_In Form Responses Table Mark Rows With Duplicate Entries As Invalid

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

mimicre said:
Copy Only Unique Form Responses?
I want to collect form responses, but only those that return unique values. Respondents will be given six drop-down selections to make, and I want to eliminate duplicate responses from the final tally--essentially whenever a respondent selects the same answer more than once, as I don't see a way to control that in the form itself.
I set up a sample form/spreadsheet here:
https://spreadsheets.google.com/spreadsheet/ccc?key=0Ah7NmG121zTFdHNHMUxGMEF4T0FQUVM1U3BBUkZZWnc&hl=en_US
(Obviously in the actual form, there would be more than six possible responses.)
Is there a formula that can help me? Or multiple formulas, even. I've tried filtering, IF(NE(B2:B,D2:H)), etc. and nothing seems to work; I can only mark the duplicate cells as invalid, not the whole row.
I would be grateful for any help anyone can give!
------------------------------------------------------------

yogi_Query A Range To Meet Certain Criteria Where A Column Has Mixed Text And Numeric Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

aussie scott said ...
I have the following questions in relation to the following document.
FS! is a sheet where a form "lands" as people clock on and off. Because I can only have 1 form per doc I ask the people to fill in a start time on starting their work and in the finished time question the default is 'At Work', when completing their shift they fill in the start time again and a finished time. Therefore you can see when people are available 'At Work' and use the completion form to calculate there hours worked. I was then expecting to be able to set up a individuals record sheet to track there hours worked and the book keeper refer to this in calculating their payment. I would like the result to be as in sheet "BS result I want" - all of a persons entries except their shift start entries. When I write a query formula as in "BS where" I get all of their entries - as expected. However when I write the formula with a second parameter as is "BS where and" I don't then get the result I expect. Frustratingly I don't know why or how to amend the formula. Could you amend =QUERY(FS!A1:H ; "Select B,F,E,D,C,G,H where B = 'Bob Smith' and H != 'At Work' Order by F,E,D,G" ; 1) so I get the "BS result I want".
Secondly is the a method of using cell A1 in each sheet and formula instead of the name ('Bob Smith') in each formula?
Many thanks for taking the time to help.
-------------------------------------------------------------

In sheet Resul01, since Column H in the sheet FS is of mixed data type ... so in the process of QUERYing I converted the range of interest A:H to TEXT using FS!A:H&"" ... so all the result of the QUERY are TEXT elements ... but that should not be a problem because the expected numerics are easily coerced into true numerics if so desired.

In sheet Result02, I used both QUERY and FILTER as the primary functions

yogi_Query A Range To Meet Certain Criteria Where A Column Has Mixed Text And Numeric Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

aussie scott said ...
I have the following questions in relation to the following document.
FS! is a sheet where a form "lands" as people clock on and off. Because I can only have 1 form per doc I ask the people to fill in a start time on starting their work and in the finished time question the default is 'At Work', when completing their shift they fill in the start time again and a finished time. Therefore you can see when people are available 'At Work' and use the completion form to calculate there hours worked. I was then expecting to be able to set up a individuals record sheet to track there hours worked and the book keeper refer to this in calculating their payment. I would like the result to be as in sheet "BS result I want" - all of a persons entries except their shift start entries. When I write a query formula as in "BS where" I get all of their entries - as expected. However when I write the formula with a second parameter as is "BS where and" I don't then get the result I expect. Frustratingly I don't know why or how to amend the formula. Could you amend =QUERY(FS!A1:H ; "Select B,F,E,D,C,G,H where B = 'Bob Smith' and H != 'At Work' Order by F,E,D,G" ; 1) so I get the "BS result I want".
Secondly is the a method of using cell A1 in each sheet and formula instead of the name ('Bob Smith') in each formula?
Many thanks for taking the time to help.
-------------------------------------------------------------
Column H in the sheet FS is of mixed data type ... so in the process of QUERYing I convert the range of interest A:H to TEXT using FS!A:H&"" ... so all the result of the QUERY are TEXT elements ... but that should not be a problem because the expected numerics are easily coerced into true numerics if so desired . So here we go ...

yogi_Sum Values From Rows Different Than The Corresponding Matching Row

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

ryangibsonstewart said:
How do I use QUERY to return a different row than specified?
A
1 | Patrols & | Youth &
| Troops | Adults
2 | Patrol A | 10
3 | 13 |
4 | |
5 | Patrol B | 2
6 | 13 | 4
7 | |
8 | Patrol C | 7
9 | 57 | 2
10| |
The numbers under the patrol names represent Boy Scout Troop numbers. The top numbers (next to the patrol names) are the number of youth that will be assigned to the patrol. The bottom number (next to the Troop number) is the number of adults that will be assigned to the patrol. (I know this isn't the best way to set up a spreadsheet, but this works visually for patrol assignments, and this is the way that the 100 or so Troop leaders understand the information.)
I want to be able to count the total number of youth and adults from a given Troop. So I want to be able to return the numbers:
Troop 13: 12 youth
Troop 13: 4 adults
Troop 57: 7 youth
Troop 57: 2 adults
I'm stuck. I tried using the QUERY formula, but I could only get it to return numbers in the same row (meaning the adult numbers, not the youth numbers). I also couldn't get it to SUM any numbers. Hmm.
Any help would be greatly appreciated. Thanks!
FYI, I use Google Chrome, on Windows 7.
-----------------------------------------------
here is one way ...

yogi_Extract Penultimate (last but one) Non-Zero Entry From D5:D17

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Justadrumer said:
Next to last non zero from list ...
I need to know if there is a Google spreadsheet function that finds next to last text in a column. The column in question will only have one column filled with a text value and the rest of the values will be evaluated to zero. I found the following post which helped me get pull the last non zero from the column but I'd also like to pull the next to last as a separate result. Thoughts?http://www.google.ru/support/forum/p/Google+Docs/thread?tid=2b2c982b8ac59299&hl=en
---------------------------------------------------------

yogi_Compute Rank Based On Values In A Row Of Columns In A Specified Pattern - 2

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

gookon said ...

(for my earlier Blog post on gookon's project see ... yogi_Compute Rank Based On Values In A Row Of Columns In A Specified Pattern

http://yogi--anand-consulting.blogspot.com/2011/08/yogicompute-rank-based-on-values-in-row.html)

 Hello again yogia,sorry for the late response,had to go out of town for a day.In the following spreadsheet,the 4th column under each player shows points awarded to that player after each fixture.The 5th column under each player is a total of the points awarded to that player after each fixture.I am using the 3rd column under each player to determine that players rank from the 5th column under each player.

While the worksheet is currently working correctly(with only 5 test players),it will not if there are identical rank values,that can be fixed but become extremely long when many more players are added.Your method,if it can be tweaked to work correctly would be a far more economical method.
https://spreadsheets.google.com/spreadsheet/ccc?key=0Aqj7-eahzGqPdFhIaDdVVnNvSUthQVhyRWFhUW05MWc&hl=en_US

-------------------------------------------------------------------

In my earlier post I computed the rank for points in the same row ... however that was based on forward data only to alleviate the issue of circular referencing.

So in this case, I have considered computing the rank in one row down from the row where the player points are recorded

yogi_Extract Time_In And Time_Out By Name From Google Form TimsStamp

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Office Time Lord said ...
How do I move some text around how I want it
OK the office has a Google Sites website. On this website, I have used Google Spreadsheet to embed a form used as a time clicker. Basicallt there is one multiple choice question (In or Out) and a submit button. This logs a username, a time stamp and an in or out response to the spreadsheet. This is fine and dandy, it meets my needs but I want to make the report on the backend a bit more robust and easier to read. The log has time-date in one column the username in next column and in or out in the last column. what i would like to have as a report is date, username, in time, out time in a row (essentially combining the in entry and out entry into one dated row with 2 times).
here's an example of what I have and what I want . . .
https://spreadsheets.google.com/spreadsheet/ccc?key=0Ap7aoNyuHz6sdHpydzR2YlJHX1U2eHlWaXlYaWRWdFE&hl=en_US
I have made a data manipulation sheet that makes the timestamp a text and the split command to separate the dates and times. I'm not sure how to combine the 2 occourances of names and times to one date. Any suggestions?
---------------------------------------------------------------

yogi_Sum A Cell Across Multiple Sheets Within A Spreadsheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

scottborys said ...
sum across multiple spreadsheets using INDIRECT
I have a variable-sized list of sheet names in A9:A100 corresponding to certain sheets in the current spreadsheet. I'm trying to sum the same cell from each spreadsheet. For example, this would work if I knew that I would always have two sheets:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E17" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E17" ))
However, due to the fact that the number of sheets will be changing rather frequently, modifying the formula each time isn't an option. Any thoughts on how to achieve the sum across multiple sheets using INDIRECT?
After that, I'll need to figure out how to drag the formula down vertically, ie, the next formula would be the equivalent of:
sum(INDIRECT( CHAR( 39 ) & A9 & CHAR( 39 ) & "!E18" ), INDIRECT( CHAR( 39 ) & A10 & CHAR( 39 ) & "!E18" ))
But we can cross that bridge when we come to it! Anyone have any thoughts on how to tackle this beast?
------------------------------------------------------
here we go ...

I have housed the cell of interest in B2
I have listed all the sheets that might be of interest in range A9:A -- I have listed the sheets of interest in cell A9 and down and I can go to cell A99 or even beyond depending on how many sheets are involved in computing the SUM

yogi_Reformat 10-Digit Phone Numbers In Form Responses Sheet For Uniformity

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

In this spreadsheet 10-digit phone numbers in Form Responses sheet have been entered by submitters in various styles with one or more of the following characters, /,-,., space character, (, and 0. I then I take the 10-digit phone numbers as they were entered and using a computed column (shown with brown colored background) I present them formatted in one uniform style across the board.
-------------------------------------------------------

yogi_Extract Sales Amount By SalesPerson By Month From A Table of SalesData

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

In this spreadsheet I extract sales data by SalesPerson by Month from a table of sales data by date, SalesPerson and salesAmount

yogi_Extract A Random Name From Within A Subset Of Data

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

trimonth said ...
How do I query a random number from within a subset of numbers?
ok how about this? There is a list of ids, groups and names. See the example
ids are numbers 1-9.
groups are red, white and blue.
names are the names.
I want to have a cel that displays a random name from the list. Any name on the list. I think I have got that. (Although if you see room for improvement, please say so.)
Now suppose I want a random name from within the list dependent on the groups. For example, I want a random name from the blue group. So it should be either joe, jill, ben, or bob. Does anyone have any ideas on how to do that?
--------------------------------------------------------
In the following solution I used primarily the FILTER function to extract a random name in Sheet2 from data in Sheet1

yogi_Assemble Membership In Select Entities From Roster Of Membership

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

NWRESD Steve said:
I have 30 people on this committee and I have to post a survey/form to find out all the committees they are on. Unfortunately, I don't have a master list of committees (Also, each person might be a part of multiple committees (one person is part of 30!!!)) so a checkbox-type question is out of the question. I've set the form up so a user enters their name, and enters up to 30 committee names they are part of.
What I need to do is figure out how to extract all the unique committee name values and assign each of the members to each of their respective committees in an at-a-glance format. I know I can do this manually, but I'd like to be able to set it up so that it will show up on another sheet in a clean format automatically. I've tried doing a pivot table and inserting and org chart but this obviously hasn't proved fruitful. Something like this would be nice:
Committee 1 Committee 2 Committee 3
Member 1 Member 2 Member 1
Member 2 Member 3
Is this possible? Am I asking for the world? Any Suggestions?
---------------------------------------------------------
In the following illustration I have presented a roster of membership in committees and then in Sheet2 I have assembled a list of names that are members of specified committee

yogi_Compute Rank Based On Values In A Row Of Columns In A Specified Pattern

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

gookon said ...
Hello all.I'm having some trouble using the Rank and Offset functions and hoped someone could point me in the right direction.In the following sample for example, in C4 I am trying to enter the rank of B4 from B4,E4,I4,etc. consistently offsetting 3 cells to the right.In F4 I want to enter the rank of E4 from the same cells as C4 and so on.
A B C D E F G H I and so on....
1| 0 0 3 3 0 0
2| 3 3 3 6 1 1
3| 0 3 0 6 1 2
4| 1 4 1 7 3 5
I've been playing with variations of =Rank(B4,Offset(B4,0,3) but keep getting errors and I'm not honestly even sure if this is the best way to achieve what I want.
Any help at all would be greatly appreciated
-------------------------------------------------------
If understood your intent correctly, I propose a little change in your setup as shown in the following proposed solution ...

yogi_Pull The Highest / Lowest Values From An Array Based On Specified Condition(s)

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

chrissugden said:
How can I pull the highest or lowest value from am array returned as result of some conditions?
I have searched for this answer, but cannot find it. I want to pull the highest or lowest value after using a condition to return all matching rows:
3001 Open
3017 WIP
3017 WIP
3017 Open
3017 Complete
For the above set of data, I want to match the value 3017 and return either a single "WIP" or "Complete".
I have tried using the FILTER function, and it worked great except that it fills my spreadsheet with CONTINUEs. Those CONTINUEs then overwrite the lookup for the next row, and so on.
Any suggestions appreciated.
----------------------------------------------------
here we go ...

yogi_Counting Numbers And Strings In One Column Based On Value In Another Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

gwrath said:
Counting numbers and "string" in column C where column B = Y
OK so I have been trying to count the occurrences of "1" and the charcters "BF" which appear in column C based on the presence of a client name in column B - I am basically trying to summarise by client the instances of these in another sheet.
I basically want the count of instances of 1 and BF in column C in the DATA sheet to appear in column C of the SUMMARY sheet.
I have tried using CountA but don't know how to make it only look at the rows containing the correct client, the number of these rows will of course be variable.
--------------------------------------------------

yogi_Operate On Top And Bottom Cells Of A Range Of Interest

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

SuperUser said:
Add top and bottom of a column (not whole column)
I have several spreadsheets with different length columns of numbers. I want to subtract the top number (always in row 2) from the last number in the column, which could be in any row. Ex: If the list goes from F2 through F10, and is empty, I want =F10-F2. If the list goes from G2 through G14, and G15 is empty, I want =G14-G2).
What I really need is a way to identify the bottom filled cell (the next cell will always be empty). Anyone know how to do that?
-----------------------------------------------

yogi_Send Form Answers To A Specific Collection

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Brandon Leahy said ...
Send Form Answers to a specific collection
I am trying to keep my acupuncture practice paper free, and have set up forms to do SOAP notes (Subjective, Objective, Assessment, Plan; basic notes for medical clinics) and would like the answers to go to specific Collections/Patient Charts. I have three different clinics with corresponding Sub-Collections, all within the collection "Chart Notes". Is there a way to automatically send the answers to a chart note based on the answers to 'Last Name' and 'First Name'? Ex: When filling out John Smith's notes for July 26, 2011, I would love to hit submit and have the answers sent to the collection 'Smith, John' in 'Chart Notes'
If it makes it easier, I can put all chart note folders into one collection so I don't have to worry about sending them to a specific clinic's collection then to a specific patient. I know there are Filter Formulas out there, but I am a complete novice/n00b when it comes to spreadsheets and their formula/filter equations.
----------------------------------------------------------
In the following I have a generalized solution where the data collected in a Form can be sent to a sheet by Individual or by Group Name.

here is the Form that I used ...


and here is the spreadsheet ...

yogi_Consolidate Data Form Different Columns By Matching Rows In Two Different Sheets

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Heooo said:
How can I compare ragged financial data?
I am trying to compare Vanguard VTI against another product here [1].
1. The problem is that [1] lacks valuations during some days, diverting results a lot.
2. And one problem more the Google Docs returns days in the format "%D/%M/%Y 16:00" while [1] returns them in the format "%D.%M.%Y".
I got the Vanguard valuations with
=GoogleFinance("VTI", "price", "29/12/2006", "8/8/2011", "daily")
that returns information in the form:
Date Close
29/12/2006 16:00:00 70.105
03/01/2007 16:00:00 69.975
04/01/2007 16:00:00 70.115
05/01/2007 16:00:00 69.56
08/01/2007 16:00:00 69.815
09/01/2007 16:00:00 69.815
10/01/2007 16:00:00 69.97
to a ragged local data in the form:
Date Close EU
29.12.2006 10
02.01.2007 9.928
04.01.2007 9.991
05.01.2007 10.042
09.01.2007 10.041
10.01.2007 10.07
I need to get the different day -format matching working before I can get the filter-match -function working. Does Google Docs can return the day in my specified format such as "%D.%M.%Y" so I do not need to do awkward parsing here?
---------------------------------------------
As you will notice the date format used for the problem in this post is in  dd.mm.yyyy style
following is one way where I have primarily used the MATCH function and the VLOOKUP function

yogi_Find And Replace Text Within A Formula

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

bdburke87 said:
Find and replace text within a formula
I have a block of cells that have formulas referencing another sheet in the same Google Doc. I would like to duplicate these formulas but with a reference to a different sheet in the same Google Doc. These two sheets that are being referenced are identical in every way except this name.
Original Formula Example 1: =Countif(Gettemeier!I3:I39, "FL")
Desired New Formula: =Countif(Ploesser!I3:I39, "FL")
Original Formula Example 2: =ARRAYFORMULA(SUM(IF('Gettemeier'!G45:G77="LS", 'Gettemeier'!B45:B77, 0)))
Desired New Formula 2: =ARRAYFORMULA(SUM(IF('Ploesser'!G45:G77="LS", 'Ploesser'!B45:B77, 0)))
As you can see, I would simply to replace text within each formula, then copy the new formulas into my desired location in the same Google Doc. I have tried SUBSTITUTE, REPLACE, and REGEXREPLACE. I have also tried Showing Formulas and Finding and Replacing. I also copied these formulas to Excel and trying to alter them to be copied back into Google Docs.
Is there a way to Find and Replace text within a formula? Or is there another way to solve this problem?
----------------------------------------------------
Well, one way would be to
1) first convert the formula into a text string
2) then use Find and replace command to change Gettemeier to Ploesser
or
to use the INDIRECT function as illustrated in the following

yogi_Match Cell Data On Two Sheets And Merge In ResultsSheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

rbjassoc said:
match cell data on 2x seperate spreed sheets and merge the result
I have 2x separate customer list saved in separate spreadsheets. Each customer has a unique email address. But the 2x spreadsheets have different data about that customer. I would like to use 1x spreadsheet as a master and merge the additional data from the second spreadsheet only to those unique email addresses that are on the first spreadsheet.
example sheet 1 example sheet 2 result sheet
email data email data email data data
j@et 10 j@et 20 j@et 10 20
g@wt 5 g@wt  45 g@wt 5 45
t@sd 7 r@ww  34
t@sd 14 t@sd 7 14
anyway I hope it is clear
-----------------------------------------------------
It is not clear whether rbjassoc means different spreadsheets (workbooks) or different sheets (worksheet). Earlier on the assumption that rbjassoc did indeed mean different spreadsheets for which I posted a solution in  ... http://yogi--anand-consulting.blogspot.com/2011/08/yogimatch-cell-data-on-two-spredsheets.html

However, it turns out that rbjassoc might have meant to say Sheet1, Sheet2, and ResultsSheet are in the sam spreadsheet. So, here we go ...