Saturday, April 13, 2013

yogi_Pull Data From Sheet1 By Matching TimeStamp Dates In Sheet1 To Specified Dates In Sheet3


                                          Google Spreadsheet   Post  #1117
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 13, 2013
user David X :(https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/doPl81JQsik)
Query with multiple conditions comparing sheet cells
Hi there, 
Thanks in advance for any help,

I've been working with 3 sheets.

Sheet 1
(Random Data)

Sheet 2
(Queried / Filtered Data)

Sheet 3
(Conditions)

In this case, I pull all the data from Sheet 1 to Sheet 2 Using this query: =QUERY(Sheet1!A2:M;"SELECT A,B,C,D,E,F,G,H,I,J,K,L,M") which works just fine.

This is there the problem starts, For instance,

- I have 2 specific cells in Sheet3, both are Dates that I use to filter "From and To"
- Column B of Sheet1 is a Text Date '2013-04-11 and such.
- I compare both cells using Value(Sheet1 Cell) which returns in this case: 41375

I came up with this statement: 

WHERE value(B) >= value('Sheet3'!K3) AND value(B) <= value('Sheet3'!K4)"

So all together I Have:  

=QUERY(Sheet1!A2:M;"SELECT A,B,C,D,E,F,G,H,I,J,K,L,M WHERE value(B) >= value('Sheet3'!K3) AND value(B) <= value('Sheet3'!K4)")

- Gsheet reports: error: Invalid query: Query parse error: Encountered " <ID> "value "" at line 1, column 38. Was expecting one of: "(" ... "(" ...
 
I tried several combinations and sintax delimeters but still same type of problem.

Thank you.
---
Hi there Hyde, 

It doesnt let me use the formula, about a problem with quotes,

please take a look at a dummy live version of the spreadsheet: 


Best Regards, 
David
------------------------------------------------------------------------------------------------------------------
following is a solution to the problem


No comments:

Post a Comment