/docs/community?hl=en
/docs/community?hl=en
8/20/12
Original Poster
david douglas

Filtering Data from ImportRange...

I have Spreadsheet "A" that is the primary information. 
I have Spreadsheet "B" that is using ImportRange from Spreadsheet "A".

On Spreadsheet "B" I am I have a filter data on one of the columns. Problem is when I change information on Spreadsheet "A" and refresh the screen on Spreadsheet "B" the filter is not updating. I have to click on the filter and click okay. Then it shows the new information. 

Is there anyway to get the filter wo update automatically. 

Thank you. 
David
Community content may not be verified or up-to-date. Learn more.
All Replies (6)
Yogi Anand
8/20/12
Yogi Anand
Hi David:

ImportRange fuction can be finicky at times ... and there might be a bit of delay in updating the imported data as well

If you care to share your spreadsheet with some sample but realistic data, I want to see if you are making multiple importrange function calls and if there is a way to avoid doing that.

Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
8/20/12
Original Poster
david douglas
Here are the two spreadsheets:
Spreadsheet 1
Spreadsheet 2

Spreadsheet 2 is pulling data from A1 to A9. I setup a filter at the top to remove the blanks. 

If you go into spreadsheet 1 and put data in cells A1 to A9 and skip a cell or two you will see that in spreadsheet 2 the information changes but it does not update the filter. 

If you click on the filter and remove the check mark next to the blanks and click okay you will see the new results. 

Does that make sense? 

David 
Yogi Anand
8/20/12
Yogi Anand
Hi David:

I noticed that you are using FILTER command, not the FILTER function ... that is why it is not updating for you.

So, if you are open to a workaround, use the following formula in cell A1 of Sheet1 of Spreadsheet2 ...

=query(importrange("0Atg3aj29hUNRdExsUHR0bFlzRThPUGlHcGl6eDd3TUE","A:B"),"select* where Col1='"&E1&"' ",1) 
   where E1 houses the name by which you to filter the imported data in column A.

Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com

ICU Pearl
10/31/13
ICU Pearl
Hi Yogi,
I have created a tabulated output of the data based on "Responses" spreadsheet. I'd like to enable user to view the filtered data based on the input in B1 of spreadsheet "Dashboard". How do I go about getting only the relevant data to be imported to C3 of the latter. So also for the remaining cells in D8:I12.
"Responses" : https://docs.google.com/spreadsheet/ccc?key=0Aqc3Vh83ndmwdHJYVzdEN2JBdTVOaWFteF9MTnA5TkE#gid=0
"Dashboard": https://docs.google.com/spreadsheet/ccc?key=0Aqc3Vh83ndmwdHJYVzdEN2JBdTVOaWFteF9MTnA5TkE#gid=5

Formula used: =query(ImportRange("0Aqc3Vh83ndmwdHJYVzdEN2JBdTVOaWFteF9MTnA5TkE","Responses!A2:A6"),"select* where Col1=date '"&text(B1,"yyyy-MM-dd")&"' ",9)

Instead of getting filtered data, all cell contents are imported to the designated cell. Your advise will be much appreciated. Many thanks!

Yogi Anand
10/31/13
Yogi Anand
Hi ICU Pearl:

how about ...
taking one formula at a time, you

a) tell me what you are trying to accomplish
b) in which cell?
    of which sheet?
c) show me your expected result
     along with needed logic/explanation
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

ICU Pearl
11/2/13
ICU Pearl
Hi Yogi, 

Background: 
Spreadsheet "Responses" is the database. 
Spreadsheet "Dashboard" is what the user sees. 
2 sheets are in the same file in my sample DB here as I was still trying out. Ultimately, 2 sheets could be located at different excel files. 
Spreadsheet "Dashboard" will be accessible from a designated google site. 

Basically the database include 5 sets of data (6 items for each set/unit) for each day. Aim is to get a simple tabular output that the viewer can understand rather than the excel sheet format.  

a) What I'd like to achieve: 
Once the user chooses a specific date at Dashboard!B1, all relevant data in the spreadsheet "Responses" to be displayed in the way tabulated in spreadsheet "Dashboard" 

b) Destination cells: 
Dashboard!C3: to populate relevant data under column A when Responses!I=Dashboard!B1 
Dashboard!D8: to import data from Responses!C when Responses!I=Dashboard!B1 AND when Responses!B=Dashboard!A8
Dashboard!E8: to import data from Responses!D when Responses!I=Dashboard!B1 AND when Responses!B=Dashboard!A8 

and so on... for Dashboard!D8 to I12


Note: Dashboard!F4 is the formula I had attempted based on your previous discussion dated 8/20/12. 
 
c) Desired output 



Appreciate your advise. Thanks. 
Pearl 
Were these replies helpful?
How can we improve them?
 
This question is locked and replying has been disabled. Still have questions? Ask the Help Community.

Badges

Some community members might have badges that indicate their identity or level of participation in a community.

 
Expert - Google Employee — Googler guides and community managers
 
Expert - Community Specialist — Google partners who share their expertise
 
Expert - Gold — Trusted members who are knowledgeable and active contributors
 
Expert - Platinum — Seasoned members who contribute beyond providing help through mentoring, creating content, and more
 
Expert - Alumni — Past members who are no longer active, but were previously recognized for their helpfulness
 
Expert - Silver — New members who are developing their product knowledge
Community content may not be verified or up-to-date. Learn more.

Levels

Member levels indicate a user's level of participation in a forum. The greater the participation, the higher the level. Everyone starts at level 1 and can rise to level 10. These activities can increase your level in a forum:

  • Post an answer.
  • Having your answer selected as the best answer.
  • Having your post rated as helpful.
  • Vote up a post.
  • Correctly mark a topic or post as abuse.

Having a post marked and removed as abuse will slow a user's advance in levels.

View profile in forum?

To view this member's profile, you need to leave the current Help page.

Report abuse in forum?

This comment originated in the Google Product Forum. To report abuse, you need to leave the current Help page.

Reply in forum?

This comment originated in the Google Product Forum. To reply, you need to leave the current Help page.