Excel - Expanding Table with Various Results

[BrianCarlow]

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!

[#2]

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

[#3]

@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?

[#4]

Use VSTACK() to stack both tables.

[#5]

Hi,
That will not work either as POs and Invocies have different data so that will not work.

[#6]

Then do HSTACK().