I am trying to pick up certain data within a date range

Posted by Emma on August 22, 2001 3:32 AM

I have a stock issue list , with date issued ,month of issue , product issued, and quantity.
On a seperate worksheet I have a list of all products, with opening stock , and I would like a column with stock issued (only for one month ) for example all stock issued in August . How can i do this ?
If anyone is interested in helping , I can send you the file .

Posted by Rob Jackson on August 22, 2001 4:20 AM

Check out DSUM function. Full details in the help file.

Rob

Posted by Aladin Akyurek on August 22, 2001 5:10 AM

Emma,

You have the following data in range A4:D10 in sheet Issues:

{"Date","Month","Product","Quantity Issued";0,0,0,0;37147,9,"Bubbaloo Peppermint 60",10;37155,9,"Bubbaloo Cherry 60",50;37155,9,"Bubbaloo Cola 60",210;37155,9,"Bubbaloo Cherry 60",251;37165,10,"Bubbaloo Strawberry 60",50}

Note 1. The big numbers are your dates as they are stored internally in Excel, so don't worry about them. 0's represent blank cells.

You want a total of stock issued in a particular month per product in another worksheet (called Sum). This worksheet is organized from A1 on like:

{"Movement by month",0,0,"Month",9;0,0,0,0,0;0,0,0,0,0;"Product","Opening Stock","Issues","Receipts","Closing Stock";0,0,0,0,0;"Bubbaloo Strawberry 60",250,0,0,250;"Bubbaloo Tutti Fruitti 60",0,0,0,0;"Bubbaloo Peppermint 60",0,10,0,10;"Bubbaloo Banana 60",0,0,0,0;"Bubbaloo Cola 60",0,210,0,210;"Bubbaloo Cherry 60",0,301,0,301;"Bubbaloo Green Apple 60",0,0,0,0}

What you want is a formula in C6 that produces a total of stock issued in a particular month (as given in E1) with respect to the product in A6. This formula has to be copied down up to the last product listed in A.

I reckon that the data in Issues change regularly by new additions. Taken this into account:

Activate Issues.
Activate Insert|Name|Define.
Enter LastRow as name in the Names in Workbook box.
Enter the following in the Refers To box:

=MATCH(9.99999999999999E+307,Issues!$A:$A)

Activate Add (don't leave the Define Name window).

Enter also DATES as name in the Names in Workbook box.
Enter the following in the Refers To box:

=OFFSET(Issues!$A$6,0,0,LastRow,1)

Activate Add (don't leave the Define Name window).

Enter also PRODUCTS as name in the Names in Workbook box.
Enter the following in the Refers To box:

=OFFSET(Issues!$C$6,0,0,LastRow,1)

Activate Add (don't leave the Define Name window).

Enter also QUANTITIES as name in the Names in Workbook box.
Enter the following in the Refers To box:

=OFFSET(Issues!$D$6,0,0,LastRow,1)

Activate OK.

Note 2. The above setup enables you to use the range names instead of the ranges themselves. "These names" track the changes made to data.

On sheet Sum,

in C6 enter: =SUMPRODUCT((PRODUCTS=A6)*(MONTH(DATES)=$E$1)*(QUANTITIES))

Copy down this formula as far as needed.

Note 3. You can delete your Month column in Issues. The above formula doesn't use it.

Note 4. You can also use PivotTables to compile a summary of your data in Issues. The only drawback is that you need refresh the computations.

Aladin