Dynamic Range for multiple columns

[Robin]

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 M]

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),COUNTA(Sheet1!$D$D)),2)

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

 

[Robin]

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

 

[Gord Dibben]

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

 

[Robin]

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]

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

Insert>Name>Define................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.

F5>Goto

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 C2234 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

 

[Robin]

Gord,

Concerening this question
Assume you currently have data in C2234 do you want your DynAbb to include
that range and expand when you add more rows?
I want the expansion to be in both rows because they are related. The first
row represents the total term and the 2nd row represents a different version
of the same term. They are both reliant on one another. It is up to the user
to make the selection that they would prefer. I have listed a certain number
of terms that are similar in an individual row. I am trying to add to the
bottom of the list so that the user has an opportunity to add the terms that
they would prefer to select from if there were none that they could use from
the pre typed list. The ability of the user to add to the C and D column is
what I am trying to accomplish.

Robin

 

[Robin]

Gord,

I have 2007 version and I have gone to Formulas tab and then select name
define
Then I type in DynAbbrMeasure
I select list (is there something else that I should select?)
I then Paste =OFFSET(Measure!$C$2,0,0,COUNTA(Measure!$C:$C),2)
Click ok
Press F5
In the go to box it states D236236
I type in DynAbbrMeasure
It states that the Reference is not valid

What am I doing wrong? The end of the typed list is C 234 & D 234 and I
would like the range to be added to to and indefinite range. I would
appreciate your help with this.

Sincerely,

Robin