On 2002-09-04 05:00, gnaga wrote:

Thanks Aladin

I have used your option no : 1. It is working fine. I would like to know how to use the other two options also. Can you please tell me how?

Thanks

GNaga

Option 2. Defining a Name That Refers To a Changing Range By Means of a Dynamic Formula (Dynamic Range Name)

( 1.) Insert a worksheet and name it, e.g.,

**Admin**.

( 2.) Activate Insert|Name|Define.

( 3.) Enter

**BigNum** as name in the box for

**Names in Workbook**.

( 4.) Enter the following in the box for

**Refers to**;

9.99999999999999E+307

( 5.) Click OK.

The data of interest in your case is in A:C in Sheet2 and column A houses dates which makes this column of numeric type.

( 1.) Activate Admin.

( 2.) In A2 enter: # of rows [ which is just a label ]

( 3.) In B2 enter:

=MATCH(BigNum,Sheet2!A:A)

( 4.) In A3 enter: # of data recs [ which is just a label ]

( 5.) In B3 enter:

=B2-(CELL("Row",Sheet!A2)-1)

The preceeding formula refers to the cell in A where real data starts.

( 6.) In A4 enter: # of data columns [ which is just a label ]

( 7.) In B4 enter: 3 [ hardcoded number of columns in use ]

( 8.) Activate Insert|Name|Define.

( 9.) In the

**Names in Workbook** box, enter

**DTable** (from data table) as name.

(10.) In the

**Refers to** box, enter the following formula:

=OFFSET(Sheet2!$A$2,0,0,Admin!$B$3,Admin!$B$4)

(11.) Click OK.

Now, you should be able to reference in formulas any column from

**DTable** using, for example,

INDEX(DTable,0,1)

which corresponds to Sheet2!$A$2:$A$[row num of the last used cell in A].

The formula

=SUMPRODUCT((Sheet2!$A$2:$A$7=A2)*(Sheet2!$B$2:$C$7))

that I suggested can now be reexpressed as:

=SUMPRODUCT((INDEX(DTable,0,1)=A2)*(INDEX(DTable,0,2)))

The 1 in the first INDEX refers to column A and the 2 in the second INDEX to column B.

Option 3. The UDF Route.

( 1.) Copy the following code:

Function Used(r As Range) As Range

'

' Harlan Grove

' Sun 25 Nov 01

'

Dim q As Range

Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)

Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))

End Function

( 2.) Open the target workbook.

( 3.) Activate Tools|Macro|Visual Basic Editor.

( 4.) Activate Insert|Module.

( 5.) Paste the copied code in the window entitled "...(Code)".

( 6.) Activate File|Close and Return to Microsoft Excel.

The formula

=SUMPRODUCT((Sheet2!$A$2:$A$7=A2)*(Sheet2!$B$2:$C$7))

that I suggested can now be reexpressed using

**Used** as:

=SUMPRODUCT((Used(Sheet2!$A:$A)=A2)*(Used(Sheet2!$B:$C)))

This message was edited by Aladin Akyurek on 2002-09-04 11:06