Expanding a Range Automatically

[Guest]

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.

 

[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)

 

[Guest]

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

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)

 

[Guest]

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

Thanks Bob - Exactly what I need.

 

[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)

 

[Ed K]

Apparently not.

Ed

 

[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