1

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.

  • 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
  • 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
  • No, that would definitely work, but it defeats the purpose of having dynamically pulled data :/ I would have to manually copy/past every time I updated the "Owners" sheet. – gcoulson Jul 15 '14 at 20:57
  • 1
    Good point! Updated :) – gcoulson Jul 15 '14 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.