multiple column dynamic range

[Robin]

I have a range from C2234. The column C has a heading of term and Column D
is the abbreviaton. I want the cells starting wiht C235 and D 235 to be able
to have additions made by the user. I have been given instructions to do the
following

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 and an indefinite range. I would
appreciate your help with this.

Sincerely,

Robin

 

[Don Guillett]

You did say version 2007?

Formulas>defined name>in the new name box type in a name>in the REFERS TO
box>put in your offset formula>OK

 

[Dave Peterson]

What's in column C?

If there's not enough to count, then you may not have a legal range/reference.

ps. If you're not sure what's in C1 all the time, you may want to use a formula
like:

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

Then the range (if it's legal!) will start in row 2.

 

[Gord Dibben]

Selecting "list" was not the instructions you got from me.

These are my instructions and comments using the formula you got from Luke.

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)


Gord Dibben MS Excel MVP

 

[Robin]

Dave

I went to formulas tab>namebox> typed in DynAbbrMeasure, did not select
anything from the scope which I had selected list before, pasted your formula
of =OFFSET(Measure!$C$2,0,0,COUNTA(Measure!$C:$C)-COUNTA(Measure!$C$1),2)
clicked ok, pressed Key F5, typed in the reference DynAbbrMeasure, clicked
ok, and a box appearred that I had an invalid reference. If you have any
other suggestions I would be appreciative.

 

[Robin]

C11 is a topic

Cells C ARE words andCells D are abbreviations. I do not know what a legal
range reference.

 

[Robin]

Gord
This is what I did ( Yes, you did not tell me to select list)

I did not select any cells on the excel sheet; however there is always a box
that has bold black borders.

Selected Formulas.Namebox
Type DynAbbrMeasure
Left the scope dropdown at the default worksheet but I think it might be
appropriate to select list because I would like the user to select from or
add to the list.

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

Clicked OK

PressedF5

type in DynAbbrMeasure
Clicked ok
A window popped up that I had an invalid reference.

You guys have helped me. I hope that you can troubleshoot the method that I
have employed.

Sincerely,

Robin

 

[Don Guillett]

Apparently you won't be able to get this done so.....
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Dave Peterson]

If your formula evaluated to use start in Row 1 but extend the range to 12 rows,
that would be legal.

But if that formula evaluated to start in row 1 but extend for 0 or a negative
number of rows, then that would not be legal.

Are you sure that the worksheet is named Measure?

Maybe there's a spelling error (or an extra space character at either end???

 

[Robin]

Dave this is the formula that I was given to use
=OFFSET(Measure!$C$2,0,0,COUNTA(Measure!$C:$C),2)
I want the initial cell to be C2 .
The initial row is C2 and D2 C2 is a list of words and D2 is a list of
corresponding abbreviations. I have typed the list to the cells C234 and
D234. I would like the cells to extend equally for as long as is appropriate
I think 100 would cover any additions that they might have. for C2 and D2
thus allowing the user to type any additions that they might have. I know tha
the 2 added prior to the last parenthesis is due to updating the range of 2
columns.
Is it that the row range that needs to be changed to an additional 100 be
included in the formula?

I also have the Measure sheet selected when I am updating the formula.

Does the Scope selection have any thing to do with this?

Concerning this legal stuff is there something I need to change in the
formula.

You have helped me for a long time and I thank you.

Robin.

 

[Dave Peterson]

The formula works perfectly for me.

I don't have any other guesses why it's not working for you.

But you didn't really answer my question about any typos in the worksheet name.

Try changing the worksheet name to A (a single character).

Then check the name to see what it refers to. Excel is smart enough to adjust
it to:
=OFFSET(A!$C$2,0,0,COUNTA(A!$C:$C),2)

If you don't see that new formula, then the name of the worksheet isn't
"Measure". There's a typo somewhere.

Dave this is the formula that I was given to use
=OFFSET(Measure!$C$2,0,0,COUNTA(Measure!$C:$C),2)
I want the initial cell to be C2 .
The initial row is C2 and D2 C2 is a list of words and D2 is a list of
corresponding abbreviations. I have typed the list to the cells C234 and
D234. I would like the cells to extend equally for as long as is appropriate
I think 100 would cover any additions that they might have. for C2 and D2
thus allowing the user to type any additions that they might have. I know tha
the 2 added prior to the last parenthesis is due to updating the range of 2
columns.
Is it that the row range that needs to be changed to an additional 100 be
included in the formula?

I also have the Measure sheet selected when I am updating the formula.

Does the Scope selection have any thing to do with this?

Concerning this legal stuff is there something I need to change in the
formula.

You have helped me for a long time and I thank you.

Robin.

 

[Robin]

Dave,

I had looked at the name of the tab earlier as you had questioned;
however I took a closer look and there was a dash there. I did not
intentionally place it there and for some reason did not notice it. I then
removed the dash and use the formula with measure and it did not give me a
fault.

You solved my self induced problem.

One more question, how can I check to see that if something is typed that
the new name and abbreviation will stay following what is already on the list.

 

[Robin]

Dave,

I clicked F5 and then i noticed the the list range plus the addition of
the C and D cell below list was surrounded by a black bold border. Thank you
again.

 

[Dave Peterson]

Your formula depends on C1 being empty.

Try that suggestion in my earliest response.

You could adjust the formula to always subtract 1, but then if C1 is emptied,
you'll have a different problem.

Dave,

I clicked F5 and then i noticed the the list range plus the addition of
the C and D cell below list was surrounded by a black bold border. Thank you
again.