Extracting info from multiple worksheets to summarize

I think the best way to do this would be with a macro.

1 option
click a "summarize" button when finish entering quantities
this would build the summary page.
Could take a while depending on the total lines of info on each of the five sheets

2 option
have a summary page with all options from all sheets already on it with formulas returning the quantities from the other sheets.
when the user goes to the summary page all lines not containing Zero quantity would be visible, all line containing Zero quantity would be hidden.
I think this would be faster as all the macro has to do is hide rows.

Can you post your file? It's too much to explain without a file.

Then you have to get into how much security is needed on the file being used by internal customers.....

Originally Posted by carstowal

.

Can you post your file? It's too much to explain without a file.

Then you have to get into how much security is needed on the file being used by internal customers.....

I am attaching the file.

I guess I am at a loss in regards to the security issue. Are you meaning from a standpoint of macros and formulas being altered?

Each page in your workbook has sub-section headings.
Is it necessary for these section headings to also be included on the summary page or do you just want the single line with the product info.

Just the line of info is easy.

I'm certain there's some wiz on this board that could accomplish pulling in the section headings, but for me it would require a serious overhaul of the layout on your current spreadsheets & helper columns.

Hi MrsHydrogeo


I'm sorry I'm trying to understand what you're looking for. Can you post a sample workbook with the expecting results.

Originally Posted by carstowal

Each page in your workbook has sub-section headings.
Is it necessary for these section headings to also be included on the summary page or do you just want the single line with the product info.

Just the line of info is easy.

I am really just wanting the line items, the headings are not necessary at all. I am just trying to avoid having items missed off of the separate sheets; but then I don't want to have one worksheet with everything listed and then have to locate the items off of about 200 lines or so.

Thanks,

I think it is much better off for you to creat a form in Access.

Not only you can have a nicer looking and more control order form, your orders will be entered into a database where you can minipulate a summary however you like.

As well, you can distribute your forms to everyone rather than adding to the "running" sheet.

Attached is your spreadsheet, revised

Every time you activate the SUMMARY worksheet it will refresh the data.
Go add or remove items on other sheets, return to the summary and it will update.

I don't doubt there's a better way, but this is my best effort.

If you want the order quantities on each sheet to return to Zero when the workbook closes, that can be added

I RECOMMEND you verify your data, I may have accidently altered something.

Please note I have added a helper column (A) on every sheet (and hid it) and reformatted all sheets to put the item price in column E so your summary is aligned
The helper column is rebuilt every time the Summary rebuilds so if you add or delete lines the procedure will still run.

This was done in v2000.

Thank YOU so very much!! This was what I was wanting to happen. I can't thank you enough!!