Help me in Writing Formula.

[gnaga]

Hi,

I have data in two worksheet as follows

Sheet1
A B
1 02-Aug-02 =Want Formula in this
2 03-Aug-02 =Want Formula in this


Sheet2
A B C
1 02-Aug-02 10 10
2 02-Aug-02 10 10
3 02-Aug-02 10 10
4 03-Aug-02 20 20
5 03-Aug-02 20 20
6 03-Aug-02 20 20


In column B of sheet1 I want the formula like =Sheet2!SUMPRODUCT(B1:B3,C1:C3)

but this range should be selected automatically by looking the column A in both sheets.

For Ex A1 in sheet1 is 02-Aug-02 so the formula should select range from sheet2 whose Column A value is matching with the sheet1 A1.

I hope you guys understand my question.


Thanks for help

GNaga
This message was edited by gnaga on 2002-09-03 08:21

 

[Aladin Akyurek]

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

where A2 houses a date in Sheet1.

 

[gnaga]

Aladin

It works fine. Can we give entire column (A:A),(B:B),(C:C) as range in sheet2? So that I can copy down the formula in sheet1.

Thanks

GNaga

 

[Aladin Akyurek]

On 2002-09-03 08:54, gnaga wrote:
Aladin

It works fine. Can we give entire column (A:A),(B:B),(C:C) as range in sheet2? So that I can copy down the formula in sheet1.

Thanks

GNaga

No. Formulas operating on arrays do not accept entire columns (or rows for that matter) as references.

Three options:

( 1.) Enter/define a name that refers to the definite range the 2nd table occupies. Use the name in the formula instead of the definite/fixed range.
( 2.) Define a name for the 2nd table by means of a dynamic formula if the range of the 2nd table frequently change. Use the dynamic name in the formula instead of the definite/fixed range.
( 3.) Use a UDF that allows you to use the whole columns as references in the formula.

Which one do you want?

 

[gnaga]

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

 

[Aladin Akyurek]

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

 

[gnaga]

Thanks you so much Aladin for your kind and very clear explanation.

Thanks Again

GNaga