I have 2 dynamic ranges which vary according to user input in a dropdown
box. I would like to create a 3rd range out of these which I am
attempting to do formulaicly using a Name embedded within the worksheet
and then return a singular value from this 3rd range (like its mean, or
sum, etc.). However, whenever I try to return this value, Excel
crashes. Does anyone know if this is possible to do?

To illustrate, here's what I mean:

1). First, I have setup 2 dynamic ranges, range1 and range2 (both
correctly specified as Named ranges).

Assume for the moment range1 refers to the numbers on Sheet1, column A,
rows 1:10. Also assume range2 refers to the numbers on Sheet1, column
B, rows 1:10.

2). I next define range3 as a named range which refers to:

range1-range2

3). Finally, I try to return sum(range3) (or any other aggregate
function) in any cell in a worksheet and Excel crashes. I've also
tried to enter a name for this value (as in Sum3 which refers to:
=sum(range3)) and this also fails.

Does anyone know if there is a workaround for this? I'm hoping to
avoid any embedded code to keep the workbook as 'light' as possible.

Any advice would be much appreciated!

-Pete


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=520580

I followed your instructons exactly in my XL97, except for the Refers to: for
Range3 I entered =Range1-Range2, and it worked exactly as expected. I had
all 3's in Range1 and all 1's in Range2 and my formula =sum(Range3) returned
20

hth
Vaya con Dios,
Chuck, CABGx3



"Peter Bernadyne" wrote:


I have 2 dynamic ranges which vary according to user input in a dropdown
box. I would like to create a 3rd range out of these which I am
attempting to do formulaicly using a Name embedded within the worksheet
and then return a singular value from this 3rd range (like its mean, or
sum, etc.). However, whenever I try to return this value, Excel
crashes. Does anyone know if this is possible to do?

To illustrate, here's what I mean:

1). First, I have setup 2 dynamic ranges, range1 and range2 (both
correctly specified as Named ranges).

Assume for the moment range1 refers to the numbers on Sheet1, column A,
rows 1:10. Also assume range2 refers to the numbers on Sheet1, column
B, rows 1:10.

2). I next define range3 as a named range which refers to:

range1-range2

3). Finally, I try to return sum(range3) (or any other aggregate
function) in any cell in a worksheet and Excel crashes. I've also
tried to enter a name for this value (as in Sum3 which refers to:
=sum(range3)) and this also fails.

Does anyone know if there is a workaround for this? I'm hoping to
avoid any embedded code to keep the workbook as 'light' as possible.

Any advice would be much appreciated!

-Pete


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=520580

That's so weird!!

You're right, it does work if the range is static (I assumed it
wouldn't).

Does anyone know if there would be any reason why this might not work
when using dynamic ranges?

Could this be symptomatic of a memory problem, perhaps?


--
Peter Bernadyne
------------------------------------------------------------------------
Peter Bernadyne's Profile: http://www.excelforum.com/member.php...fo&userid=7017
View this thread: http://www.excelforum.com/showthread...hreadid=520580