Excel - Expanding Table with Various Results

Copper Contributor

Hi There,


I have sets of data.


One being a list of Invoices: Invoice Num, Supplier Name, Amount, Project Code, Work Number, PO Number

One being a list of Purchase Orders: PO Num, Supplier Name, PO AMount, Project Code, Work Number


I want to be able to create a report, that when I enter a  Work Number or Project Code it returns a list of all the PO's associated and then all the invoices assoictaed underneath?


Is this possible?


At the moment I'm using  =CHOOSECOLS(FILTER to return a dynamic list.

However if I only leave 20 spaces between POs and Invoices and there is 21 PO's I get the spill error.

IS there a solution? Maybe Leave a space of 200 Rows and use a Hide/Unhide Blank Rows Macro?


Thanks in advance!

5 Replies
Do not put anything below the formula.
Or put the formula on a seperate sheet with nothing else.

@Detlef Lewin Hi


You see I will initially return all the PO's, but I also need the invoices underneath them?

So that will not work?

Use VSTACK() to stack both tables.
That will not work either as POs and Invocies have different data so that will not work.
Then do HSTACK().