# An Aggregate and Index Formula Issue

1. ## An Aggregate and Index Formula Issue

I have two spreadsheets in the same workbook: Inventory List and Inventory System

In the Inventory System, I would like to be able to add the Quantity in Stock from the Inventory List (Column G) to the Quantity Received (Column K) in the Inventory System and have the Receiving Running Total in the Inventory System (Column L) reflect this.

Also, in the Inventory System, I would like to be able to subtract the Quantity Issued in the Inventory System (Column O) from the Quantity in Stock in the Inventory List (Column G) and have the Issuing Running Total in the Inventory System (Column P) reflect this.

The idea is to have running totals for issuing and receiving sorted by item (Column D) in the Inventory System and have the running totals reset when the item in the Inventory System (Column D) changes.

My formula: =AGGREGATE(9,5,INDEX(K4:K37,MATCH(D4:D37,D4:D37,)):K4:K37)

How can I revise this to accomplish this? THanks for the help.

2. ## Re: An Aggregate and Index Formula Issue

I can't visualise this at all.

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

3. ## Re: An Aggregate and Index Formula Issue

Attached is my Excel file

4. ## Re: An Aggregate and Index Formula Issue

Pleas re-read my previous post (Point 2) and amend your sheet accordingly. With a blank sheet to look at, I still can't visualise this.

5. ## Re: An Aggregate and Index Formula Issue

The aggregate formula would go in the running total cell.

6. ## Re: An Aggregate and Index Formula Issue

I believe that the way this should work is that the Inventory System (IS) sheet keeps up with the inventory totals on a event by event basis while the Inventory List (IL) sheet gives you a quick overview of what is in stock so that you will know when to reorder. To accomplish this put the beginning inventory in the first few rows of the IS sheet you could then populate:
Column L using: =SUMIFS(K\$4:K4,C\$4:C4,C4)
Column P using: =SUMIFS(O\$4:O4,C\$4:C4,C4)
Column X using: =SUMIFS(\$K4:K\$4,\$C4:C\$4,C4)-SUMIFS(\$O4:O\$4,\$C4:C\$4,C4)+SUMIFS(\$S4:S\$4,\$C4:C\$4,C4)
On the IL sheet you could populate column G using:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1