Reference problem
 

Hi The
In my sheet1 I have:
colA colB
r1 10 5
r2 10 4
r3 15 10

I want to do a SUMIF in sheet2 at cell(1,1). I want to add sheet1 colB
when value of Sheet1 colA is 10. I tried. I think I am having syntex
problem
Sheets("Sheet2").cells(1,1).formular1c1="=SUMIF(Sh eets("Sheet1")!A1:A3,10,Sheets("Sheet1")!B1:B3)"


Would appreciate if anyone can help. Thanks in advance.


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=508212

Reference problem
 

In Sheet2!

=SUMPRODUCT((Sheet1!A1:A3=10)*(Sheet1!B1:B3))

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=508212

Reference problem
 

at Sheet2 - Cell A1 enter:

=SUMIF(Sheet1!$A$1:$A$3,10,Sheet1!$B$1:$B$3)

HTH


"jesmin" wrote:


Hi The
In my sheet1 I have:
colA colB
r1 10 5
r2 10 4
r3 15 10

I want to do a SUMIF in sheet2 at cell(1,1). I want to add sheet1 colB
when value of Sheet1 colA is 10. I tried. I think I am having syntex
problem
Sheets("Sheet2").cells(1,1).formular1c1="=SUMIF(Sh eets("Sheet1")!A1:A3,10,Sheets("Sheet1")!B1:B3)"


Would appreciate if anyone can help. Thanks in advance.


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=508212

Reference problem
 

Hi The
In my workbook, Name of 2 worksheets are a_data & a_report. I am
getting correct result when I am using the following in a trial sheet:

Sheets("Sheet2").Cells(1,1).Formula="=SUMIF(Sheet1 !A1:A3,10,Sheet1!B1:B3)".

But when I am using my original workbook code with the original name as
follows, its giving run-time error.:
Sheets("a_report").Cells(1,1).Formula="=SUMIF(a_da ta!A1:A3,10,a_data!B1:B3)"


--why the reference creating problem when I am chanhging just the sheet
name from Shee1 to a_data.
Thanks in advance.


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=508212

Reference problem
 

Jesmin,

Are there macros or other VB code recorded in your original that might
be causing the problem? Was the error

"Run-time error '9':
Subscript out of range"

If you have code in the original book that was recorded using the
original sheet names (i.e. Sheet!1), you'll have to update the code
with the new sheet names.

Does that help?


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=508212