Dynamic Named Range formula without using OFFSET function

[exceluser]

Is there a way to create a dynamic named ranged using the data below
without using the OFFSET function or full column references ?

The reference provided by the dynamic named range should be $A$2:$B$3.


A B C
1 Fruit Qty.
2 Apple 1
3 Orange 1
4

 

[Pete_UK]

You can use INDIRECT, like this:

refers to: =INDIRECT("Sheet1!A2:B"&COUNTA(Sheet1!$A$2:$A$5000))

I don't know why you want to avoid full column references, but I've
made this look up to row 5000 - adjust as required.

Hope this helps.

Pete

 

[exceluser]

Pete,

Thanks for the quick response.

I was trying to avoid full column references to minimize
calculation time.

Is there a way to create a similar formula without using a volatile
function ?




Exceluser

 

[exceluser]

Pete,

Why doesn't the formula below work ?

=ADDRESS(2,1):ADDRESS(3,2)

Is it because ADDRESS returns a text value rather than a
reference ?



Exceluser

 

[Pete_UK]

Yes, and the INDIRECT function converts text into recognisable ranges
(well, as long as the text is valid range).

Sometimes INDEX can be used instead of INDIRECT as a way of avoiding
volatile functions, but not in this case. However, if you are using
XL2007 or later, you might like to look into using Excel Tables, which
automatically adjust if you add new data to them.

Hope this helps.

Pete

 

[exceluser]

Pete,

Figured it out, though I ended up:

1) Using whole column and row references

2) And creating a name:

Data =INDEX(Sheet1!$A:$A,2):INDEX(Sheet1!
$1:$1048576,Sheet1!$C$2,Sheet1!$C$3)


A B C
1 Fruit Qty Helper Cells
2 Apple 1 =COUNTA($A:$A)
3 Orange 1 =MATCH("Qty",$1:$1,0)
4 Pear 1



It has the distinction of being dynamic (down and to the right,
depending on the last column referenced in C3), fast, non-volatile and
most importantly... it works.

Now heavy usage of formulas like, =VLOOKUP("Pineapple",Data,
2,FALSE) work and don't cause unnecessary calculation time every time
you modify a cell or object.

The only thing I'd like to change is not referencing every row on
the worksheet ($1:$1048576).

Can you reference a non-volatile formula in the DATA name to
calculate the last used row instead of every row in the worksheet
($1048576) ?

I ask only because it would seem that referencing a huge amount of
unused rows would create unnecessary CPU and memory usage and cause
Excel to prematurely reach a workbook / worksheet limit.



Exceluser