Thursday, April 7, 2011

yogi_query cell Value by Date according to today date


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

Placeb0 said:
I've got a table with three columns: Date (past and future, in several days difference between each value) and Data 1, like this:

Date Data1
01.04.2011 1.25
03.04.2011 2.58
07.04.2011 12.5
12.04.2011 5.6
15.04.2011 11.3


I need to filter/query in a separate sheet the value of Data1 according to current date, or last Data 1 values if the date in Date column is not equal to today date.
for example: if current date is 06.04.2011, i need it to return the Data1 value of 03.04.2011, and if current date becomes 07.04.2011, than to show the Data1 value of 07.04.2011

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

Well here we go ... in the following I used the FILTER function to extract the desired result in cell A1 of Sheet2. To alleviate the confusion of month or day numbering I used lettered month in the dates.

In Sheet3, I have used the QUERY function to extract the needed value in cell A1