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

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

https://docs.google.com/spreadsheets/d/1BaK3-xRuMqYSetMUs5oCyuEHaRzIz7FEZL641GrVFxE/edit?usp=sharing

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:

=IFERROR(ArrayFormula(VLOOKUP("Bob",Sheet2!$A1:$G1,{2,3,4,5,6,7},FALSE)))

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!

Update:

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
1  
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
1  
Good point! Updated :) – gcoulson Jul 15 '14 at 21:02

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.