I'm trying to solve an issue in google sheets concerning the transpose function.
Now for the sake of the problem, imagine I have 4 sheets: 1. Tracing: where there is a dropdown list of customer and invoice number 2. Invoice database: where all data regarding invoices are stored. 3. Validation list for the dropdownlist for the invoice number as in Sheet 1: Tracing. The 4th sheet is basically a data sheet to select the customer.
I would like to have in sheet 3. Validation list: 1. A transposed list of all invoices regarding the specific customer. So, based on the specific choice of customer, one should be able to only select invoices from the specific customer
Beneath some links to pictures of the example sheet:
Tracing tab: The overview of the tracing tab
Invoice database: The overview of the invoice database
Validation list: The overview of the validation list
What I have tried in the validation list: =IF(A3=Tracing!A2,transpose(offset('Invoice Database'!B3:B,,match(A3,'Invoice Database'!E$2:E$8,0)-1)),"")
Currently, this formula displays all invoices of all company, I would like the formula to return only invoices that are for the respective company.
This formula would work perfectly if all the customers in the invoice database have their own section. But, what I would not want to do in the invoice database is to split each customer into its own section containing the different headers.
Preferably, I would not want to work with =QUERY function. But if no other choice I will accept using the =QUERY function.
Could anyone offer me some insight?
See example sheet here: https://docs.google.com/spreadsheets/d/1Oe6OBumGhioK2-AesD7hnh6xyfhq16qyA_2BBwO47ts/edit?usp=sharing
See screenshot of how desired output should look like here: Screenshot of desired output
EDIT1: Added an example sheet
EDIT2: Added a screenshot
On your 'Validation list' tab, cell B27, try this to only bring back invoice numbers related to one company (contents of A3 below):
=transpose(filter('Invoice Database'!A:A,'Invoice Database'!E:E=A3))
