Expanding a Range Automatically
 

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.

Expanding a Range Automatically
 

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 InsertNameDefine..., 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" wrote in message
...
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.

Expanding a Range Automatically
 

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


"Bob Phillips" wrote:

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 InsertNameDefine..., 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" wrote in message
...
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.

Expanding a Range Automatically
 

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" wrote:

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


"Bob Phillips" wrote:

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 InsertNameDefine..., 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" wrote in message
...
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.

Expanding a Range Automatically
 

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)



bondtang wrote:

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" wrote:

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


"Bob Phillips" wrote:

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 InsertNameDefine..., 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" wrote in message
...
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.




--

Dave Peterson

Expanding a Range Automatically
 

Apparently not.

Ed

"Rob E" wrote:

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.

Expanding a Range Automatically
 

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

"Dave Peterson" wrote:

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)



bondtang wrote:

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" wrote:

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


"Bob Phillips" wrote:

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 InsertNameDefine..., 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" wrote in message
...
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.




--

Dave Peterson