Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #584 Jun 08, 2012 www.energyefficientbuild.com.
user VP4711 said:
Query a range with query criteria is also a range
My spreadsheet looks like this
user VP4711 said:
Query a range with query criteria is also a range
My spreadsheet looks like this
A B C D
1 Car1 BMW Car1 =query(?????)
2 Car2 Mercedes Car3
3 Car3 Porsche Car7
4 Car4 Hyundai
5 Car5 Honda
6 Car6 Ford
7 Car7 Ferrari
I do not want to use vlookup in each column D1, D2, D3. I want to use Query function to show the list in D1:D3 basis criteria in C1:C3
Formula might be
Query (A1:C7; "Select B where A = C1:C3")
and desired result is
A B C D
1 Car1 BMW Car1 BMW
2 Car2 Mercedes Car3 Porsche
3 Car3 Porsche Car7 Ferrari
4 Car4 Hyundai
5 Car5 Honda
6 Car6 Ford
7 Car7 Ferrari
Please tell me what am I doing wrong?
-----------------------------------------------------------------------------------------
you can use contain in QUERY Select as illustrated in
following solution to the problem
Hi Yogi,
ReplyDeleteI've always been a fan of your blog. Most of the work that I've done is because of some of your insights.
Anyway, I would like to ask a favor if you have time to help me out on this one.
I'm trying to come up with a project management report. My challenge is how to filter the tasks that were 100% complete in reference to the date today. I also need to get the task to be done in the next 7 days under 'work planned next week'
I've tried doing this query in the 1st tab under work completed last week.
=(QUERY (Progress Report Chart!A9:E16; "SELECT A WHERE E='100%' AND C < datetime '"&text(now()-"00:00","yyyy-mm-dd HH:mm:ss")&"' ")
Looks like the formula needs a lot of work. Help please?
I've published a dummy report here: (https://docs.google.com/spreadsheet/pub?key=0AqEpwVHEU0S1dHNhN1piS2twbHc4dkNsb3dqNlphZ0E&gid=7)
Hi Nacho:
ReplyDeleteI suggest you share your Google spreadsheet for normal view with some sample but realistic data,
let us take one formula at a time, so
a) tell me what formula you need help with
b) in which cell?
of which sheet?
c) show me your expected result
along with needed logic/explanation as to why that is the correct result
and then let us take it from there.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Yogi,
ReplyDeleteYou're totally awesome. Thanks for the prompt reply. I didn't imagine it would be this fast. I'll definitely bring in more links to your site so you would rank further in search engines.
I've shared with you the worksheet. The end goal of this project is to generate a weekly report. In the first sheet, it will show what was 100% complete and what should we be working on the next week. I want something semi-automated. When I update Column E on the Progress report chart, it will update the Gantt chart and the summary in the 1st sheet.
To clarify:
I need help on the formula on C9 and G9 on campaign progress summary. (I've updated the formula in there which looks stupid lol) What's supposed to be there are queries pulling out the list of tasks that are 100% done based from the 'progress report chart' (column A).
For c9
In the dummy data, since only items 1.3 and 1.4 are 100% complete but the end date of 1.3 is 3/10/13 and 1.4 is 3/15/13, only item 1.4 should show up in the campaign progress sheet in c9. So I would like two filters: the end date and % of completion.
For G9
For this one, I only have 1 filter: the date. Any task that should be done 7 days today should be in there. With the current data, only item 1.6 should show up.
I know I have trouble expressing myself sometimes but I do hope this conveyed what I want to accomplish.
Thanks so much Yogi!