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:
- 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").
- Each owner will have a max 5 records, for a total of 15 records in the "Owner" sheet.
- I want to only return those 5 records for each named sheet, minus the "Owner" column.
- I do not want to return empty rows, nor do I want to have to have 15 rows in each named sheet.
- 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.
- 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:
- Returns an empty row if nothing is found
- 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).
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.