# Help me in Writing Formula.

#### gnaga

##### Well-known Member
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
=SUMPRODUCT((Sheet2!\$A\$2:\$A\$7=A2)*(Sheet2!\$B\$2:\$C\$7))

where A2 houses a date in Sheet1.

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

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

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?

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

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

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.

( 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:

(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)".

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

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

Thanks Again

GNaga

Replies
6
Views
368
Replies
2
Views
235
Replies
3
Views
277
Replies
1
Views
256
Replies
1
Views
468

1,218,620
Messages
6,143,515
Members
450,492
Latest member
Rusbus1972

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back