Take the 2-minute tour ×
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.

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 at 20:26
1  
Updated original question to clarify the sorting issue. –  gcoulson Jul 15 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 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 at 20:52
1  
Good point! Updated :) –  gcoulson Jul 15 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.