Help me in Writing Formula.

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=SUMPRODUCT((Sheet2!$A$2:$A$7=A2)*(Sheet2!$B$2:$C$7))

where A2 houses a date in Sheet1.
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Thanks you so much Aladin for your kind and very clear explanation.

Thanks Again

GNaga
 

Forum statistics

Threads
1,144,329
Messages
5,723,732
Members
422,512
Latest member
MHau5

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top