Summarise Multiple Sheets

Hi All,

Hopefully someone can help a beginner!

I am trying to summarise multiple sheets into one table but not just in the ordinary way.

Each sheet is a price list for different suppliers. What I want to do is summarise this information into an invoice.

So if you enter a quantity into one of the sheets the price updates and then this populates the invoice sheet.

I have attached an example of what I am trying to achieve.

You can see the invoice tab does not have data where the qty was blank (or zero) in the other sheets. It only displays the lines that have qtys against it.

Is there a way that this can be achieved?

THANKS!

Steve.

Hi Steve,

do you intend to run a macro when the invoice is required?

Dave.

Steve,

how many suppliers and products are there likely to be?

Dave.

Please see the attached sheet which is the simplest way that I can think of.

Have one sheet with all your suppliers and products on (see sheet invoice2). Format it as required so when you've finished entering the quantities you can go straight to printing the invoice.

Once the quantities are entered select the filter on quantity column and select 'Number Filters' 'Greater Than' and enter 0.

This will 'remove' all the products with zero quantity and you can print the invoice. Once complete select the filter again and select the 'Select All' tickbox.

Dave

Hi All,

Dave, that is what I am currently doing so thanks for the suggestion. I was hoping that I could automate it a bit more because the issue is as I add more products I have to add them to the invoice sheet also.

I will be starting with 20 suppliers but this could grow to 30 in the next few months.

I do not mind running a macro, I just have no idea what it is I should be writing.

Thanks again,

Stephen

Do you mean that you want to compile Sup 1 to 3 into the invoice? so that it will be like a master list?

Yeah, pretty much. Ideally the price lists tabs will be locked. All we need to do is plug in qtys and the invoice sheet (if need be running a macro) will update to only include the products that we put volume against.

Don't know whether i get you right or not. But is this smth you want? the macro is in VBA. just run it again if you want to try.

Invoice Example.xlsm

That is exactly what I want apart from excluding the items that have no volume against it, so in this example the Gigondas and the Chateauneuf would not appear on the invoice tab.

Thanks btw!

This is what my Price List looks like - generally same colums but lines vary, hence the need to pick out only the lines that have a qty entered.

sorry, not so understand. Does that mean that all the sup is not included in?
only CLEMENT and CORRENSON included in?

Yeah, this is just an example. I have 20 estates in total.

Grace14's solution seems to do the job, however just add the following code as the last line (before 'end sub')

Worksheets("Invoice").Range("A2:E2").AutoFilter Field:=4, Criteria1:="<>"

That then filters out all the blank quantities.

Dave.

Originally Posted by thatitalian

Hi All,

Hopefully someone can help a beginner!

I am trying to summarise multiple sheets into one table but not just in the ordinary way.

Each sheet is a price list for different suppliers. What I want to do is summarise this information into an invoice.

So if you enter a quantity into one of the sheets the price updates and then this populates the invoice sheet.

I have attached an example of what I am trying to achieve.

You can see the invoice tab does not have data where the qty was blank (or zero) in the other sheets. It only displays the lines that have qtys against it.

Is there a way that this can be achieved?

THANKS!

Steve.

Here is your Solution in the enclosed sheet named "Working". Regards