Expanding a Range Automatically

R

Rob E

Is there a quick and easy way to get a range that you have entered into a
formula or feature to expand automatically.

For example, if I am creating database functions, I want my formulae to
update automatically to include new entries I add to my data list, or a pivot
table to update with new entries that are added to the list.

Any help would be appreciated.
 
B

Bob Phillips

Create a dynamic range name and use that. For instance, this defines a
dynamic range that grows as column A grows

=OFFSET($A$1,,,COUNT($A:$A),3)

The COUNT($A:$A) determines the height (number of rows), and the ,3
determines the width (number of columns).

To create a name, menu Insert>Name>Define..., name it something sensible
such as NamesRange, add that formula in the RefersTo box, and use like this

=VLOOKUP("value", NamesRange,2,False)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
R

Rob E

Thanks Bob - Exactly what I need.
--
Thanks,
Rob E.


Bob Phillips said:
Create a dynamic range name and use that. For instance, this defines a
dynamic range that grows as column A grows

=OFFSET($A$1,,,COUNT($A:$A),3)

The COUNT($A:$A) determines the height (number of rows), and the ,3
determines the width (number of columns).

To create a name, menu Insert>Name>Define..., name it something sensible
such as NamesRange, add that formula in the RefersTo box, and use like this

=VLOOKUP("value", NamesRange,2,False)


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
Click to expand...
 
B

bondtang

Hi Bob,

Is it
1) define name and in refer to input =OFFSET($A$1,,,COUNT($A:$A),3)
2) what should I input in the "range" of the pivot table?

Bond


Rob E said:
Thanks Bob - Exactly what I need.
Click to expand...
 
D

Dave Peterson

Type in the name you used in the Insert|Name dialog.

By the way, you used =count() to determine how many rows. I would have thought
that with the header (probably not a number???), =counta() would have been
better.

In fact, I'd include the sheet name, too:

=OFFSET(sheet1!$A$1,0,0,COUNTa(sheet1!$A:$A),3)
 
E

Ed K

If you try this solution in Excel 2007, it does not work. The pivot table
wizard tells you that the name (the named range you type in) does not exist.

Ed
 
Top