I want to make a worksheet where the user places a whole number value
in a cell. That value is then relied upon to define the number of rows
in a named range on another worksheet in the same workbook.

So, it would define say, the number of shelf positions on a wall.

So, the range would be from say 10 to 45 shelf positions, depending on
the user's choice of the other worksheet's referred to cell value.

Or would it be simply easier to define a number of named ranges on the
worksheet which include the numeric, then refer to that named range in my
subsequent formula scripting?

On Oct 22, 1:18*pm, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> * I want to make a worksheet where the user places a whole number value
> in a cell. *That value is then relied upon to define the number of rows
> in a named range on another worksheet in the same workbook.
>
> * So, it would define say, the number of shelf positions on a wall.
>
> *So, the range would be from say 10 to 45 shelf positions, depending on
> the user's choice of the other worksheet's referred to cell value.
>
> * Or would it be simply easier to define a number of named ranges on the
> worksheet which include the numeric, then refer to that named range in my
> subsequent formula scripting

name your range colA and in the formula
=offset($a$10,0,0,a1,1)
or better yet to make it SELF adjusting based on the content of col A
=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)

On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
<(E-Mail Removed)> wrote:

>On Oct 22, 1:18*pm, CellShocked
><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> * I want to make a worksheet where the user places a whole number value
>> in a cell. *That value is then relied upon to define the number of rows
>> in a named range on another worksheet in the same workbook.
>>
>> * So, it would define say, the number of shelf positions on a wall.
>>
>> *So, the range would be from say 10 to 45 shelf positions, depending on
>> the user's choice of the other worksheet's referred to cell value.
>>
>> * Or would it be simply easier to define a number of named ranges on the
>> worksheet which include the numeric, then refer to that named range in my
>> subsequent formula scripting
>
>name your range colA and in the formula
>=offset($a$10,0,0,a1,1)
>or better yet to make it SELF adjusting based on the content of col A
>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)


That formula goes in the what cell?

The sheet1 cell has say 40 in it, the sheet2 array would then be 40
rows x whatever number of columns.

I can sort of see what is going on here, but I do not know where to put
the formula. What? Cell A1 of sheet4? I am a bit confused.

I think Don means for you to place the formula in the "refers to"
dialog when defining the name.


Gord

On Sat, 22 Oct 2011 20:02:40 -0700, CellShocked
<(E-Mail Removed)> wrote:

>On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
><(E-Mail Removed)> wrote:
>
>>On Oct 22, 1:18*pm, CellShocked
>><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>>> * I want to make a worksheet where the user places a whole number value
>>> in a cell. *That value is then relied upon to define the number of rows
>>> in a named range on another worksheet in the same workbook.
>>>
>>> * So, it would define say, the number of shelf positions on a wall.
>>>
>>> *So, the range would be from say 10 to 45 shelf positions, depending on
>>> the user's choice of the other worksheet's referred to cell value.
>>>
>>> * Or would it be simply easier to define a number of named ranges on the
>>> worksheet which include the numeric, then refer to that named range in my
>>> subsequent formula scripting
>>
>>name your range colA and in the formula
>>=offset($a$10,0,0,a1,1)
>>or better yet to make it SELF adjusting based on the content of col A
>>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)
>
>
> That formula goes in the what cell?
>
> The sheet1 cell has say 40 in it, the sheet2 array would then be 40
>rows x whatever number of columns.
>
> I can sort of see what is going on here, but I do not know where to put
>the formula. What? Cell A1 of sheet4? I am a bit confused.

On Sat, 22 Oct 2011 23:51:50 -0700, Gord Dibben <(E-Mail Removed)> wrote:

>I think Don means for you to place the formula in the "refers to"
>dialog when defining the name.
>

I will try that. Thanks.

I still don't see it.

I named A1 on sheet1 'colA' and then I edited the name in name manager
to that formula but pointed it at sheet2 instead. But I still do not
understand.

I want to have a cell that is for defining the array size row count.

so the user see in sheet one a series of field to fill data in on. One
of those fields is used to set the number of rows in the data array.

So my sheet1 is the user data input sheet, and the results get referred
to on subsequent 'display' sheets.

>
>Gord
>
>On Sat, 22 Oct 2011 20:02:40 -0700, CellShocked
><(E-Mail Removed)> wrote:
>
>>On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
>><(E-Mail Removed)> wrote:
>>
>>>On Oct 22, 1:18*pm, CellShocked
>>><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>>>> * I want to make a worksheet where the user places a whole number value
>>>> in a cell. *That value is then relied upon to define the number of rows
>>>> in a named range on another worksheet in the same workbook.
>>>>
>>>> * So, it would define say, the number of shelf positions on a wall.
>>>>
>>>> *So, the range would be from say 10 to 45 shelf positions, depending on
>>>> the user's choice of the other worksheet's referred to cell value.
>>>>
>>>> * Or would it be simply easier to define a number of named ranges on the
>>>> worksheet which include the numeric, then refer to that named range in my
>>>> subsequent formula scripting
>>>
>>>name your range colA and in the formula
>>>=offset($a$10,0,0,a1,1)
>>>or better yet to make it SELF adjusting based on the content of col A
>>>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)
>>
>>
>> That formula goes in the what cell?
>>
>> The sheet1 cell has say 40 in it, the sheet2 array would then be 40
>>rows x whatever number of columns.
>>
>> I can sort of see what is going on here, but I do not know where to put
>>the formula. What? Cell A1 of sheet4? I am a bit confused.

On Oct 23, 8:39*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> On Sat, 22 Oct 2011 23:51:50 -0700, Gord Dibben <phnor...@shaw.ca> wrote:
> >I think Don means for you to place the formula in the "refers to"
> >dialog when defining the name.
>
> * I will try that. *Thanks.
>
> * I still don't see it.
>
> * I named A1 on sheet1 'colA' and then I edited the name in name manager
> to that formula but pointed it at sheet2 instead. *But I still do not
> understand.
>
> *I want to have a cell that is for defining the array size row count.
>
> *so the user see in sheet one a series of field to fill data in on. *One
> of those fields is used to set the number of rows in the data array.
>
> *So my sheet1 is the user data input sheet, and the results get referred
> to on subsequent 'display' sheets.
>
>
>
>
>
>
>
>
>
> >Gord
>
> >On Sat, 22 Oct 2011 20:02:40 -0700, CellShocked
> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>
> >>On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
> >><dguille...@gmail.com> wrote:
>
> >>>On Oct 22, 1:18*pm, CellShocked
> >>><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> >>>> * I want to make a worksheet where the user places a whole number value
> >>>> in a cell. *That value is then relied upon to define the number ofrows
> >>>> in a named range on another worksheet in the same workbook.
>
> >>>> * So, it would define say, the number of shelf positions on a wall..
>
> >>>> *So, the range would be from say 10 to 45 shelf positions, depending on
> >>>> the user's choice of the other worksheet's referred to cell value.
>
> >>>> * Or would it be simply easier to define a number of named ranges on the
> >>>> worksheet which include the numeric, then refer to that named range in my
> >>>> subsequent formula scripting
>
> >>>name your range colA and in the formula
> >>>=offset($a$10,0,0,a1,1)
> >>>or better yet to make it SELF adjusting based on the content of col A
> >>>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)
>
> >> That formula goes in the what cell?
>
> >> *The sheet1 cell has say 40 in it, the sheet2 array would then be 40
> >>rows x whatever number of columns.
>
> >> I can sort of see what is going on here, but I do not know where to put
> >>the formula. What? Cell A1 of sheet4? *I am a bit confused.

Send your file with a complete explanation and before/after examples
to dguillett1 @gmail.com

On Sun, 23 Oct 2011 07:26:16 -0700 (PDT), Don Guillett
<(E-Mail Removed)> wrote:

>On Oct 23, 8:39*am, CellShocked
><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> On Sat, 22 Oct 2011 23:51:50 -0700, Gord Dibben <phnor...@shaw.ca> wrote:
>> >I think Don means for you to place the formula in the "refers to"
>> >dialog when defining the name.
>>
>> * I will try that. *Thanks.
>>
>> * I still don't see it.
>>
>> * I named A1 on sheet1 'colA' and then I edited the name in name manager
>> to that formula but pointed it at sheet2 instead. *But I still do not
>> understand.
>>
>> *I want to have a cell that is for defining the array size row count.
>>
>> *so the user see in sheet one a series of field to fill data in on. *One
>> of those fields is used to set the number of rows in the data array.
>>
>> *So my sheet1 is the user data input sheet, and the results get referred
>> to on subsequent 'display' sheets.
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> >Gord
>>
>> >On Sat, 22 Oct 2011 20:02:40 -0700, CellShocked
>> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>>
>> >>On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
>> >><dguille...@gmail.com> wrote:
>>
>> >>>On Oct 22, 1:18*pm, CellShocked
>> >>><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> >>>> * I want to make a worksheet where the user places a whole number value
>> >>>> in a cell. *That value is then relied upon to define the number of rows
>> >>>> in a named range on another worksheet in the same workbook.
>>
>> >>>> * So, it would define say, the number of shelf positions on a wall.
>>
>> >>>> *So, the range would be from say 10 to 45 shelf positions, depending on
>> >>>> the user's choice of the other worksheet's referred to cell value.
>>
>> >>>> * Or would it be simply easier to define a number of named ranges on the
>> >>>> worksheet which include the numeric, then refer to that named range in my
>> >>>> subsequent formula scripting
>>
>> >>>name your range colA and in the formula
>> >>>=offset($a$10,0,0,a1,1)
>> >>>or better yet to make it SELF adjusting based on the content of col A
>> >>>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)
>>
>> >> That formula goes in the what cell?
>>
>> >> *The sheet1 cell has say 40 in it, the sheet2 array would then be 40
>> >>rows x whatever number of columns.
>>
>> >> I can sort of see what is going on here, but I do not know where to put
>> >>the formula. What? Cell A1 of sheet4? *I am a bit confused.
>
>Send your file with a complete explanation and before/after examples
>to dguillett1 @gmail.com

You know... I see this "standard answer" of your quite often.

I know you help those that/who actually do send you their materials,
but the whole concept of this group is not merely to help me, but to
illustrate my need, and the subsequent fix here, so that others may also
learn or be shown a problem, and a solution.

Considering the simplicity of most of the queries, I think you should
change your paradigm to providing folks asking for help here, with
answers which are given here.

It would not change your 'mission to help', and would actually expand
the scope of 'help' your answer could possibly provide. I really do not
understand why you do this so often. I also see many others giving links
to solutions to be found elsewhere.

Seems folks have forgotten what the forum itself is really for. It is
not simply to help the original requestor find an answer, it is so all
who may read the query may also see the answer and benefit from its
fruits.

But thanks anyway.

On Oct 23, 10:22*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> On Sun, 23 Oct 2011 07:26:16 -0700 (PDT), Don Guillett
>
>
>
>
>
>
>
>
>
> <dguille...@gmail.com> wrote:
> >On Oct 23, 8:39*am, CellShocked
> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> >> On Sat, 22 Oct 2011 23:51:50 -0700, Gord Dibben <phnor...@shaw.ca> wrote:
> >> >I think Don means for you to place the formula in the "refers to"
> >> >dialog when defining the name.
>
> >> * I will try that. *Thanks.
>
> >> * I still don't see it.
>
> >> * I named A1 on sheet1 'colA' and then I edited the name in name manager
> >> to that formula but pointed it at sheet2 instead. *But I still do not
> >> understand.
>
> >> *I want to have a cell that is for defining the array size row count..
>
> >> *so the user see in sheet one a series of field to fill data in on. *One
> >> of those fields is used to set the number of rows in the data array.
>
> >> *So my sheet1 is the user data input sheet, and the results get referred
> >> to on subsequent 'display' sheets.
>
> >> >Gord
>
> >> >On Sat, 22 Oct 2011 20:02:40 -0700, CellShocked
> >> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>
> >> >>On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
> >> >><dguille...@gmail.com> wrote:
>
> >> >>>On Oct 22, 1:18*pm, CellShocked
> >> >>><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
> >> >>>> * I want to make a worksheet where the user places a whole number value
> >> >>>> in a cell. *That value is then relied upon to define the numberof rows
> >> >>>> in a named range on another worksheet in the same workbook.
>
> >> >>>> * So, it would define say, the number of shelf positions on a wall.
>
> >> >>>> *So, the range would be from say 10 to 45 shelf positions, depending on
> >> >>>> the user's choice of the other worksheet's referred to cell value..
>
> >> >>>> * Or would it be simply easier to define a number of named ranges on the
> >> >>>> worksheet which include the numeric, then refer to that named range in my
> >> >>>> subsequent formula scripting
>
> >> >>>name your range colA and in the formula
> >> >>>=offset($a$10,0,0,a1,1)
> >> >>>or better yet to make it SELF adjusting based on the content of colA
> >> >>>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)
>
> >> >> That formula goes in the what cell?
>
> >> >> *The sheet1 cell has say 40 in it, the sheet2 array would then be40
> >> >>rows x whatever number of columns.
>
> >> >> I can sort of see what is going on here, but I do not know where toput
> >> >>the formula. What? Cell A1 of sheet4? *I am a bit confused.
>
> >Send your file with a complete explanation and before/after examples
> >to dguillett1 * * * *@gmail.com
>
> * You know... *I see this "standard answer" of your quite often.
>
> * I know you help those that/who actually do send you their materials,
> but the whole concept of this group is not merely to help me, but to
> illustrate my need, and the subsequent fix here, so that others may also
> learn or be shown a problem, and a solution.
>
> * Considering the simplicity of most of the queries, I think you should
> change your paradigm to providing folks asking for help here, with
> answers which are given here.
>
> * It would not change your 'mission to help', and would actually expand
> the scope of 'help' your answer could possibly provide. *I really do not
> understand why you do this so often. *I also see many others giving links
> to solutions to be found elsewhere.
>
> * Seems folks have forgotten what the forum itself is really for. *Itis
> not simply to help the original requestor find an answer, it is so all
> who may read the query may also see the answer and benefit from its
> fruits.
>
> * But thanks anyway.

As you say, I do help people. AND, I always post back my answers or
ask the OP to do so for the benefit of all. However,I really do not
have the time nor inclination to recreate projects for the purpose of
testing. So, I ask for a file.
I DID try to answer your question HERE. In fact, I did....... You
simply did not understand how to define a name and make it variable.
So, instead of spending time, as I am doing now, in trying, again and
again to make you understand, I simply took the easiest way for me to
ask for your file.I wish you LUCK.....

On Sun, 23 Oct 2011 11:36:14 -0700 (PDT), Don Guillett
<(E-Mail Removed)> wrote:

>AND, I always post back my answers

I was unaware of this aspect. Sorry.

On Sun, 23 Oct 2011 11:36:14 -0700 (PDT), Don Guillett
<(E-Mail Removed)> wrote:

>On Oct 23, 10:22*am, CellShocked
><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> On Sun, 23 Oct 2011 07:26:16 -0700 (PDT), Don Guillett
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> <dguille...@gmail.com> wrote:
>> >On Oct 23, 8:39*am, CellShocked
>> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> >> On Sat, 22 Oct 2011 23:51:50 -0700, Gord Dibben <phnor...@shaw.ca> wrote:
>> >> >I think Don means for you to place the formula in the "refers to"
>> >> >dialog when defining the name.
>>
>> >> * I will try that. *Thanks.
>>
>> >> * I still don't see it.
>>
>> >> * I named A1 on sheet1 'colA' and then I edited the name in name manager
>> >> to that formula but pointed it at sheet2 instead. *But I still do not
>> >> understand.
>>
>> >> *I want to have a cell that is for defining the array size row count.
>>
>> >> *so the user see in sheet one a series of field to fill data in on. *One
>> >> of those fields is used to set the number of rows in the data array.
>>
>> >> *So my sheet1 is the user data input sheet, and the results get referred
>> >> to on subsequent 'display' sheets.
>>
>> >> >Gord
>>
>> >> >On Sat, 22 Oct 2011 20:02:40 -0700, CellShocked
>> >> ><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>>
>> >> >>On Sat, 22 Oct 2011 13:16:15 -0700 (PDT), Don Guillett
>> >> >><dguille...@gmail.com> wrote:
>>
>> >> >>>On Oct 22, 1:18*pm, CellShocked
>> >> >>><cellshoc...@thecellvalueattheendofthespreadsheet.org> wrote:
>> >> >>>> * I want to make a worksheet where the user places a whole number value
>> >> >>>> in a cell. *That value is then relied upon to define the number of rows
>> >> >>>> in a named range on another worksheet in the same workbook.
>>
>> >> >>>> * So, it would define say, the number of shelf positions on a wall.
>>
>> >> >>>> *So, the range would be from say 10 to 45 shelf positions, depending on
>> >> >>>> the user's choice of the other worksheet's referred to cell value.
>>
>> >> >>>> * Or would it be simply easier to define a number of named ranges on the
>> >> >>>> worksheet which include the numeric, then refer to that named range in my
>> >> >>>> subsequent formula scripting
>>
>> >> >>>name your range colA and in the formula
>> >> >>>=offset($a$10,0,0,a1,1)
>> >> >>>or better yet to make it SELF adjusting based on the content of col A
>> >> >>>=OFFSET(Sheet4!$A$10,0,0,MATCH("*",Sheet4!$A:$A,-1)-9,1)
>>
>> >> >> That formula goes in the what cell?
>>
>> >> >> *The sheet1 cell has say 40 in it, the sheet2 array would then be 40
>> >> >>rows x whatever number of columns.
>>
>> >> >> I can sort of see what is going on here, but I do not know where to put
>> >> >>the formula. What? Cell A1 of sheet4? *I am a bit confused.
>>
>> >Send your file with a complete explanation and before/after examples
>> >to dguillett1 * * * *@gmail.com
>>
>> * You know... *I see this "standard answer" of your quite often.
>>
>> * I know you help those that/who actually do send you their materials,
>> but the whole concept of this group is not merely to help me, but to
>> illustrate my need, and the subsequent fix here, so that others may also
>> learn or be shown a problem, and a solution.
>>
>> * Considering the simplicity of most of the queries, I think you should
>> change your paradigm to providing folks asking for help here, with
>> answers which are given here.
>>
>> * It would not change your 'mission to help', and would actually expand
>> the scope of 'help' your answer could possibly provide. *I really do not
>> understand why you do this so often. *I also see many others giving links
>> to solutions to be found elsewhere.
>>
>> * Seems folks have forgotten what the forum itself is really for. *It is
>> not simply to help the original requestor find an answer, it is so all
>> who may read the query may also see the answer and benefit from its
>> fruits.
>>
>> * But thanks anyway.
>
>As you say, I do help people. AND, I always post back my answers or
>ask the OP to do so for the benefit of all. However,I really do not
>have the time nor inclination to recreate projects for the purpose of
>testing. So, I ask for a file.
>I DID try to answer your question HERE. In fact, I did....... You
>simply did not understand how to define a name and make it variable.
>So, instead of spending time, as I am doing now, in trying, again and
>again to make you understand, I simply took the easiest way for me to
>ask for your file.I wish you LUCK.....

I do know how do define a name, and upon editing said definition in
name mgr, His formula fails as I have no "sheet4", so I edit it to
sheet2. Still, when one types a name in, it usually transports you there
and highlights the cell or array.

It was the cell A1 referenced as a numeric whole number value which
defines the row count in an array on sheet2 (or a named range thereof).
This is so a form on sheet2 will have a dynamic row count. I want to be
able to adjust both the start row number and the end, with the end being
based on the number in sheet1_A1.

So essentially, it is a variable sized form for printing purposes, which
based on the value previously referred to, the form fills the page with
'n' number of lines between the form header and tally/totalizer lines.

I notice when I name a range and *I* physically insert row *within*
that named range, it automagically expands the named range's definition.

Adding rows at the end does not, so this is the only thing I can think
of to make it easy for the user to expand the form, yet still retain a
declared range name correctly encompassing the range.