I would like to create a dynamic range for multiple columns that are
attributed to the same name for a list. The reason for the dynamic range is
that I would like the user to be able to add to the list. If they had other
data that they would like to incorporate into the list.

I am able to make a list of mutliple columns for a single list and there
are multiple entries.

OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) is the example they under
create a dynamic range what changes would need to be made to allow this to be
done for multiple columns

Say if the columns that I am interested in are c and d
I have tried the following formula and it does not seem to work. How does
the list appear when it automatically expands does the darkened border expand
around the list.

OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$D),1)

If you look at the arguements for OFFSET, you'll see that the last number
controls how many columns to return. (the previous controls length). For your
situation, I'd write:

=OFFSET(Sheet1!$C$1,0,0,MAX(COUNTA(Sheet1!$C:$C),C OUNTA(Sheet1!$D$D)),2)

The MAX function is there to determine which column (C or D) is controlling
the size of your range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Robin" wrote:

I would like to create a dynamic range for multiple columns that are
attributed to the same name for a list. The reason for the dynamic range is
that I would like the user to be able to add to the list. If they had other
data that they would like to incorporate into the list.

I am able to make a list of mutliple columns for a single list and there
are multiple entries.

OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) is the example they under
create a dynamic range what changes would need to be made to allow this to be
done for multiple columns

Say if the columns that I am interested in are c and d
I have tried the following formula and it does not seem to work. How does
the list appear when it automatically expands does the darkened border expand
around the list.

OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$D),1)

Luke,

Your instruction is good;however I have another problem. It is that
initially I made an original list and named it new. I then wanted to create a
dynamic list and named it newdyn that could be updated. I used your formula
it was accepted by the computer but I do not know how to be able to select it
to updated the list. I have read somewhere that you have to type it somewhere
to automatically acces it. I have tried multiple ways but cannot get it to
work.

How do I acces it to be able to use it. Is there a way to put it in the
upper left box with the regular list.

sincerely

Robin

Sincerely,

Robin
"Luke M" wrote:

If you look at the arguements for OFFSET, you'll see that the last number
controls how many columns to return. (the previous controls length). For your
situation, I'd write:

=OFFSET(Sheet1!$C$1,0,0,MAX(COUNTA(Sheet1!$C:$C),C OUNTA(Sheet1!$D$D)),2)

The MAX function is there to determine which column (C or D) is controlling
the size of your range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Robin" wrote:

I would like to create a dynamic range for multiple columns that are
attributed to the same name for a list. The reason for the dynamic range is
that I would like the user to be able to add to the list. If they had other
data that they would like to incorporate into the list.

I am able to make a list of mutliple columns for a single list and there
are multiple entries.

OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) is the example they under
create a dynamic range what changes would need to be made to allow this to be
done for multiple columns

Say if the columns that I am interested in are c and d
I have tried the following formula and it does not seem to work. How does
the list appear when it automatically expands does the darkened border expand
around the list.

OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$D),1)

Dynamic range names won't show in the Name Box for selection.

Hit F5 and type the name into the reference dialog.


Gord Dibben MS Excel MVP

On Fri, 31 Jul 2009 16:34:01 -0700, Robin
wrote:

Luke,

Your instruction is good;however I have another problem. It is that
initially I made an original list and named it new. I then wanted to create a
dynamic list and named it newdyn that could be updated. I used your formula
it was accepted by the computer but I do not know how to be able to select it
to updated the list. I have read somewhere that you have to type it somewhere
to automatically acces it. I have tried multiple ways but cannot get it to
work.

How do I acces it to be able to use it. Is there a way to put it in the
upper left box with the regular list.

sincerely

Robin

Sincerely,

Robin
"Luke M" wrote:

If you look at the arguements for OFFSET, you'll see that the last number
controls how many columns to return. (the previous controls length). For your
situation, I'd write:

=OFFSET(Sheet1!$C$1,0,0,MAX(COUNTA(Sheet1!$C:$C),C OUNTA(Sheet1!$D$D)),2)

The MAX function is there to determine which column (C or D) is controlling
the size of your range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Robin" wrote:

I would like to create a dynamic range for multiple columns that are
attributed to the same name for a list. The reason for the dynamic range is
that I would like the user to be able to add to the list. If they had other
data that they would like to incorporate into the list.

I am able to make a list of mutliple columns for a single list and there
are multiple entries.

OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) is the example they under
create a dynamic range what changes would need to be made to allow this to be
done for multiple columns

Say if the columns that I am interested in are c and d
I have tried the following formula and it does not seem to work. How does
the list appear when it automatically expands does the darkened border expand
around the list.

OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$D),1)

Gord,

It states my reference is not valid. I selected C2&D2 throughout C234 &
D234
I then type in the formula that Luck had given me Except that I named it
according to the tab name which is Measure and it started in C2 so I changed
the reference number to 2, I wanted the rows to be able to be offset by 10
(which I assume means that 10 items can be added) I will paste the Offset
formula for the dymamic range
=OFFSET(Measure!$C$2,10,0,MAX(COUNTA(Measure!$C:$C ),COUNTA(Measure!$D:$D)),2).
I then click on F5 and the cell number D35 is in the reference box. I then
type in the name that I gave to the selection in the Name box of
DynAbbrMeasure and then it states that the reference is not valid. I do not
know what I am doing wrong. I would appreciate your input concerning this.

"Gord Dibben" wrote:

Dynamic range names won't show in the Name Box for selection.

Hit F5 and type the name into the reference dialog.


Gord Dibben MS Excel MVP

On Fri, 31 Jul 2009 16:34:01 -0700, Robin
wrote:

Luke,

Your instruction is good;however I have another problem. It is that
initially I made an original list and named it new. I then wanted to create a
dynamic list and named it newdyn that could be updated. I used your formula
it was accepted by the computer but I do not know how to be able to select it
to updated the list. I have read somewhere that you have to type it somewhere
to automatically acces it. I have tried multiple ways but cannot get it to
work.

How do I acces it to be able to use it. Is there a way to put it in the
upper left box with the regular list.

sincerely

Robin

Sincerely,

Robin
"Luke M" wrote:

If you look at the arguements for OFFSET, you'll see that the last number
controls how many columns to return. (the previous controls length). For your
situation, I'd write:

=OFFSET(Sheet1!$C$1,0,0,MAX(COUNTA(Sheet1!$C:$C),C OUNTA(Sheet1!$D$D)),2)

The MAX function is there to determine which column (C or D) is controlling
the size of your range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Robin" wrote:

I would like to create a dynamic range for multiple columns that are
attributed to the same name for a list. The reason for the dynamic range is
that I would like the user to be able to add to the list. If they had other
data that they would like to incorporate into the list.

I am able to make a list of mutliple columns for a single list and there
are multiple entries.

OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) is the example they under
create a dynamic range what changes would need to be made to allow this to be
done for multiple columns

Say if the columns that I am interested in are c and d
I have tried the following formula and it does not seem to work. How does
the list appear when it automatically expands does the darkened border expand
around the list.

OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$D),1)

When creating the dynamic range you do not select any particular cell or
range of cells

InsertNameDefine................type a name.........I'll use DynAbb

In the "refers to" box enter

=OFFSET(Measure!$C$2,10,0,MAX(COUNTA(Measure!$C:$C ),COUNTA(Measure!$D:$D)),2)

OK your way out.

F5Goto

Type in DynAbb

With the formula above C12 to 10 blank cells below last used cell in column
D will be selected.

I don't think that's what you want but you said to Luke......"your
instruction is good"

Assume you currently have data in C2:D234 do you want your DynAbb to include
that range and expand when you add more rows?

If column C and D will contain same number of used rows then

=OFFSET(Measure!$C$2,0,0,COUNTA(Measure!$C:$C),2)

would suffice.

Which is basically what you started with.


Gord

On Sat, 1 Aug 2009 15:03:01 -0700, Robin
wrote:

Gord,

It states my reference is not valid. I selected C2&D2 throughout C234 &
D234
I then type in the formula that Luck had given me Except that I named it
according to the tab name which is Measure and it started in C2 so I changed
the reference number to 2, I wanted the rows to be able to be offset by 10
(which I assume means that 10 items can be added) I will paste the Offset
formula for the dymamic range
=OFFSET(Measure!$C$2,10,0,MAX(COUNTA(Measure!$C:$ C),COUNTA(Measure!$D:$D)),2).
I then click on F5 and the cell number D35 is in the reference box. I then
type in the name that I gave to the selection in the Name box of
DynAbbrMeasure and then it states that the reference is not valid. I do not
know what I am doing wrong. I would appreciate your input concerning this.

"Gord Dibben" wrote:

Dynamic range names won't show in the Name Box for selection.

Hit F5 and type the name into the reference dialog.


Gord Dibben MS Excel MVP

On Fri, 31 Jul 2009 16:34:01 -0700, Robin
wrote:

Luke,

Your instruction is good;however I have another problem. It is that
initially I made an original list and named it new. I then wanted to create a
dynamic list and named it newdyn that could be updated. I used your formula
it was accepted by the computer but I do not know how to be able to select it
to updated the list. I have read somewhere that you have to type it somewhere
to automatically acces it. I have tried multiple ways but cannot get it to
work.

How do I acces it to be able to use it. Is there a way to put it in the
upper left box with the regular list.

sincerely

Robin

Sincerely,

Robin
"Luke M" wrote:

If you look at the arguements for OFFSET, you'll see that the last number
controls how many columns to return. (the previous controls length). For your
situation, I'd write:

=OFFSET(Sheet1!$C$1,0,0,MAX(COUNTA(Sheet1!$C:$C),C OUNTA(Sheet1!$D$D)),2)

The MAX function is there to determine which column (C or D) is controlling
the size of your range.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Robin" wrote:

I would like to create a dynamic range for multiple columns that are
attributed to the same name for a list. The reason for the dynamic range is
that I would like the user to be able to add to the list. If they had other
data that they would like to incorporate into the list.

I am able to make a list of mutliple columns for a single list and there
are multiple entries.

OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) is the example they under
create a dynamic range what changes would need to be made to allow this to be
done for multiple columns

Say if the columns that I am interested in are c and d
I have tried the following formula and it does not seem to work. How does
the list appear when it automatically expands does the darkened border expand
around the list.

OFFSET(Sheet1!$C$1,0,0,COUNTA(Sheet1!$C:$D),1)

To create a dynamic range for multiple columns attributed to the same name for a list, you can use the following formula:

Here, we have used the COUNTA function to count the number of non-empty cells in column C and included a width of 2 to include both columns C and D.

1. When the user adds data to the list, the range will automatically expand to include the new data.
2. The darkened border will expand around the list to show the new range.

Note that you can adjust the starting cell reference (Sheet1!$C$1) to match the starting cell of your list and adjust the column reference (Sheet1!$C:$C) to match the column range of your list.