inventory control in excel

[artisanpp]

Hi Domenic

Further to our last discussion, the cells A2 to A12 would not have the items listed in any particular order.
The items indicated as sold would be listed on the sheet, these would change. As a customer purchases an item it would be listed in the column A2 to A12
His sheet which would double as his invoice may only list 1 item or it may have 10 items he purchased.

The sheet names would also, always be different.
As far as VBA goes I haven't a clue what that might be.

best regards
gino

[Domenic]

Hi Gino,

Assumptions:

A1:A10 on your 'master sheet' contains your list of sheet names

Column B on your 'master sheet' contains your unique list of products

A2:A12 for each sheet contains the products sold

B2:B12 for each sheet contains the number of items sold

Formula:

C1, copied down:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$10&"'!A2:A12"),B1,INDIRECT("'"&$A$1:$A$10&"'!B2:B12")))

As I mentioned before, the drawback with this is that each time you add a new sheet you'll need to update your list of sheet names, in this example A1:A10, and change the reference in the formula.

Hope this helps!

[Domenic]

Here's an alternative...

On each sheet do the following...

1) Enter your unique list of product numbers in an out of the way column. So for example if you have 10 different products, enter them in IU1:IU10 in the same order for each sheet.

2) For each sheet, enter the following formula in IV1 and copy down:

=SUMIF($A$2:$A$12,IU1,$B$2:$B$12)

Insert a new sheet, name it 'First', and make sure it's the first sheet in your workbook. Insert another new sheet, name it 'Last', and make sure it's the last sheet in your workbook.

On your master sheet do the following...

1) Enter your list of products in the same order as other sheets, let's say A1:A10.

2) Enter the following formula in B1 and copy down:

=SUM('First:Last'!IV1)

Now, every time you add a new sheet, place it between the 'First' and 'Last' sheet. Your totals should automatically be updated. Also, you can hide Columns U and V for each of your sheets.

Hope this helps!

[artisanpp]

Hi Domenic

I have been away on a family emergency and have just returned.
Thank you for posting the information for me, I will give this a try in the next couple days.

Thanks again for your time
best regards
gino