Sign up ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

This seems pretty straight forward, and I've seen some answers that come close, but I'm having trouble nailing down one specific requirement.

What I want to do:

  1. For each named sheet (Bob, Jim, Andy), I want to get the records/rows from "Owners" based on the name (ex Value "Bob" for sheet "Bob").
  2. Each owner will have a max 5 records, for a total of 15 records in the "Owner" sheet.
  3. I want to only return those 5 records for each named sheet, minus the "Owner" column.
  4. I do not want to return empty rows, nor do I want to have to have 15 rows in each named sheet.
  5. The data in the named sheets (Bob/Jim/Andy) needs to remain dynamic as I want to only have to edit data in the "Owners" sheet.
  6. The data in the named sheets needs to be sortable by people with view-only privileges.

I've managed to get the corresponding data via:


Issue with this solution:

  1. Returns an empty row if nothing is found
  2. Requires 15 rows in each named sheet to ensure no records/rows are missed.

Anyway, stuck, help much appreciated!


I managed to have a bit more success using QUERY, and running:

=QUERY(Owners!$A$2:$G$16, "select B where (A = 'Bob')")

And then having this at the top of each column. The issue here being that, while the results are initially sorted, any additional sorting (Z-A specifically) adds a large number of rows to the sheet (I'm getting a total of 510 rows after the sort).

Update 2:

Incorporated pnuts suggestion of:

=QUERY(Owners!$A$2:$G$16, "select B, C, D, E, F, G where (A = 'Bob')

Sorting issue remains an issue.

share|improve this question
They appear sorted initially, but when you resort them (Z-A), the sheet adds empty rows (I'm getting 510 total rows after the sort). – gcoulson Jul 15 '14 at 20:26
Updated original question to clarify the sorting issue. – gcoulson Jul 15 '14 at 20:38
Odd! Is =QUERY(Owners!$A$2:$G$16, "select B, C, D, E, F, G where (A = 'Bob') ORDER BY B") any use to you? Alternative might be to copy, Paste special, Paste values only before any sorting. – pnuts Jul 15 '14 at 20:41
That's definitely cleaner in regards to getting all the data copied over, but it still suffers from the same sorting bug :( I also noticed that the Z->A sort doesn't actually work. – gcoulson Jul 15 '14 at 20:52
Good point! Updated :) – gcoulson Jul 15 '14 at 21:02

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.