Cell Reference issue
 

I have a formula:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!N:N,ROW(Sheet 1!N2:N65536)
-ROW(Sheet1!N1),0,1)),--(Sheet1!N2:N65536="Dog"))

Calculating the sheet takes forever because of the cell range.
Sometime the sheet will have 10 rows other times it will have 10,000
rows, so I put the range N2:N65536. I already have a count of the
number of rows in a cell. Can I refer to that cell in the range, for
example: N2:N&B1 (where B1 has the number of rows)?

I tried it a ton of ways but can't get it to work. Any input would be
appreciated.
:confused:


--
KMartin
------------------------------------------------------------------------
KMartin's Profile: http://www.excelforum.com/member.php...o&userid=37164
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

The most efficient way would probably be

N2:INDEX(N:N,B1)

You could also use

=INDIRECT("N2:N"&B1)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

Thank you...using N2:INDEX(N:N,B1) definitely has me on the right track.


I am getting a #Value! error, but when I evaluate the error the cell
reference is showing the range I want.


--
KMartin
------------------------------------------------------------------------
KMartin's Profile: http://www.excelforum.com/member.php...o&userid=37164
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

One more question...

I think my error is because I am referencing another sheet in the
workbook. If I move my formula over to Sheet1, it seems to work fine.
I know I read something about getting the error if you reference a
closed workbook, but both sheets are in the same workbook. Do I need
to refer to Sheet1 or Sheet2 differently?

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!N:N,ROW(Sheet 1!N2:INDEX($N:$N,Sheet2B1))-ROW(Sheet1!N1),0,1)),--(Sheet1!N2:INDEX($N:$N,Sheet2!B1)="High"))


--
KMartin
------------------------------------------------------------------------
KMartin's Profile: http://www.excelforum.com/member.php...o&userid=37164
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

If the formula isn't in sheet1 then I think you need "sheet1!" before
the $N:$N, i.e.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!N:N,ROW(Sheet 1!N2:INDEX(Sheet1!$N:$N,Sheet2!B1))-ROW(Sheet1!N1),0,1)),--(Sheet1!N2:INDEX(Sheet1!$N:$N,Sheet2!B1)="High"))


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

Worked like a charm - Pure Genius! Thank you very much!


--
KMartin
------------------------------------------------------------------------
KMartin's Profile: http://www.excelforum.com/member.php...o&userid=37164
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

Glad it worked for you.

Just out of interest, I can't quite see what the SUBTOTAL/OFFSET part
of the formula is giving you, can you explain what it's supposed to
count?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

daddylonglegs wrote...
If the formula isn't in sheet1 then I think you need "sheet1!" before
the $N:$N, i.e.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!N:N,ROW(Shee t1!N2:INDEX(Sheet1!$N:$N,
Sheet2!B1))-ROW(Sheet1!N1),0,1)),--(Sheet1!N2:INDEX(Sheet1!$N:$N,Sheet2!B1)="High"))

This particular formula is equivalent to

=COUNTIF(Sheet1!N:N,"High")

which is nonvolatile. It's highly likely this COUNTIF formula would be
even more efficient.

Cell Reference issue
 

Harlan Grove Wrote:


This particular formula is equivalent to

=COUNTIF(Sheet1!N:N,"High")



Harlan,

thanks

that's what I was thinking, hence my query above. I just didn't quite
trust my analysis, thought I might have missed something....


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=568826

Cell Reference issue
 

I am allowing users to filter results and then they can recalculate the
other sheets based on the filter. The formula I used seemed to work
the best for that.


--
KMartin
------------------------------------------------------------------------
KMartin's Profile: http://www.excelforum.com/member.php...o&userid=37164
View this thread: http://www.excelforum.com/showthread...hreadid=568826