Would you like to give access to an anonymous copy? On Friday, April 26, 2013 10:23:41 PM UTC-5, DirtyPinkPig wrote: > > Using iMac, OSX 10.6.8 > > I was going to try to use FILTER(), but I was informed that it can only do > 1 dimensional booleans, so that idea's out. > > I just learned about the QUERY() function. I've done some reading and > watched some videos, but none of the ones (I've found) have discussed > methods on how to solve my problem: > > BACKGROUND INFO: > (See attached screenshots) > > Sheet 'Scheduling' has 13 weekly calendars, with each cell representing a > 15 minute block of time. X axis is days of the week, Y axis is hour of the > day. User copies from C6 down and pastes special values only into one of > the calendars to the right. The names of each calendar represents a > different room: cal_00, cal_01, cal_02... cal_12, with a corresponding > named and protected range for each (G5:N73 for cal_00, etc). > > Scheduling!C6:C73 purposefully has a period "." in each cell. When the > user copies and pastes into the calendars to the right, conditional > formatting kicks in to change the font color to match the green color, so > they're blended in. > > An example of what a block of time reserved for an event is shown in > sched_01.tiff > > THE PROBLEM: > sched_02 and sched_03 are screenshots of the sheet > 'Printout_by_Instructor_QUERY'. My goal for this sheet is to pull from all > 13 calendars on 'Scheduling' all events associated with a given > instructor's name (ie Johnson, Anderson, Smith, etc). The instructor's name > can be chosen via Data Validation pull-down menu in cell A1. > > The formula in 'Printout_by_Instructor_QUERY' cell A3 is currently: > > =QUERY(cal_00, "select G, H, I, J, K, L, M, N", 1) > > I tried changing it to this, just to see if I could get it to pull any > occurrences of 1) the name 'Johnson' 2) empty cells, or 3) a period '.' in > the calendar cal_00 on Mondays only. > > *=QUERY(cal_00, "select G, H, I, J, K, L, M, N where H='' or H = > 'Johnson' or H = '.' ", 1)* > > It seems as though the effect of H='' (which is two single quotes) isn't > the same as ISBLANK(). It sort of indiscriminately includes everything when > I do that. It also does this with H contains ''. > > I've tried H <> '' and H != '' but it leaves everything blank. > > I don't know if QUERY is the right tool for the job after all, since it > has the tendency to want to cut out empty cells. If not QUERY, then what? > FILTER? > > Your help is much appreciated! > > > > > >
-- You received this message because you are subscribed to the Google Groups "Google Spreadsheets API" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
