Re: Expanding a Range Automatically

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

"Rob E" <Ro...@discussions.microsoft.com> wrote in message
news:5E73F307-3139-48A9...@microsoft.com...
> 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.
> --
> Thanks,
> Rob E.

[Rob E]

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

[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

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

--

Dave Peterson

[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