Current Region in a formula

[Greg Lovern]

Is it possible for a formula to return the current region, using only
Excel's built-in functions (no UDFs)?

I understand this would be easy in a UDF; my question is whether it
can be done without a UDF.

For example, if I have hundreds of regions of cells on a sheet, each
region anywhere from about 5 rows to a few hundred rows, and I get new
source data daily with different regions and different sizes of
regions, and I want to use Rank down a new column to get each row's
rank within its own region, is there a way to specify the current
region in Rank's ref argument?


Thanks,

Greg

 

[Ron Rosenfeld]

Is it possible for a formula to return the current region, using only
Excel's built-in functions (no UDFs)?

I understand this would be easy in a UDF; my question is whether it
can be done without a UDF.

For example, if I have hundreds of regions of cells on a sheet, each
region anywhere from about 5 rows to a few hundred rows, and I get new
source data daily with different regions and different sizes of
regions, and I want to use Rank down a new column to get each row's
rank within its own region, is there a way to specify the current
region in Rank's ref argument?


Thanks,

Greg

You might be able to do that depending on how the region is delineated.