How do I have a cell value define a dynamic named range?

[CellShocked]

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?

 

[Don Guillett]

  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)

 

[CellShocked]

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.

 

[Gord Dibben]

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


Gord

 

[CellShocked]

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.

 

[Don Guillett]

  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.

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

 

[CellShocked]

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.

 

[Don Guillett]

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

 

[CellShocked]

AND, I always post back my answers

I was unaware of this aspect. Sorry.

 

[CellShocked]

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.

 

[Don Guillett]

  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.

Let's see if you can do this
Insert>name>define>name Print_Area
in the refers to box COPY this ONE line below to the refers to
box>click OK
change "header" & the last 3 to your desired COLUMN NUMBER. When you
change the sheet you will immediately see the new print area
outlined.
=OFFSET(INDIRECT("sheet2!$a$"&MATCH("header",Sheet2!$A:$A,0)),
0,0,MATCH("*",Sheet2!$A:$A,-1)-MATCH("header",Sheet2!$A:$A,0)+1,3)

 

[CellShocked]

Insert>name>define>name Print_Area

Excel 2007 and 2010 does not have this menu structure.

I *can* go into the name manager and redefine the "Print_Area" name.

Note however where I mentioned the use of "a" header. NOT the system
header and footer.

Thanks though. I think somehow you have failed to see what it isI am
after. You never make a mention of the CELL I want to be REFERENCED to AT
ALL TIMES in this. The size of the dynamic range MUST be based on the
value of THAT number.

I did NOT ask for the standard "dynamically expanding range" stuff.

I wanted a dynamic range that gets its size set hard and fast, based on
the value in that cell. I keep seeing dynamic ranges, but no way for the
range size to have been based on that cell value.

SO either I missed something in you responses, or you missed something
in the criteria and gave your response from an assumption of what "I must
be wanting".

I will try what you have here (thanks), but I doubt it will be what I
am after. I know I mentioned a "header" but I wasn't referring to the
system's print job header in this case. I was referring to my form and
attempting to differentiate for you that it (the dynamically expanding
zone) falls in the center of a non-dynamic set of rows both above and
below.

 

[Don Guillett]

 Excel 2007 and 2010 does not have this menu structure.

  I *can* go into the name manager and redefine the "Print_Area" name.

 Note however where I mentioned the use of "a" header.  NOT the system
header and footer.

 Thanks though.  I think somehow you have failed to see what it isI am
after. You never make a mention of the CELL I want to be REFERENCED to AT
ALL TIMES in this.  The size of the dynamic range MUST be based on the
value of THAT number.

 I did NOT ask for the standard "dynamically expanding range" stuff.

  I wanted a dynamic range that gets its size set hard and fast, based on
the value in that cell.  I keep seeing dynamic ranges, but no way for the
range size to have been based on that cell value.

  SO either I missed something in you responses, or you missed something
in the criteria and gave your response from an assumption of what "I must
be wanting".

  I will try what you have here (thanks), but I doubt it will be what I
am after.  I know I mentioned a "header" but I wasn't referring to the
system's print job header in this case. I was referring to my form and
attempting to differentiate for you that it (the dynamically expanding
zone) falls in the center of a non-dynamic set of rows both above and
below.

Perhaps you may now understand WHY I offered to have you send me your
file. Always nice to mention excel version in the OP. In the name
manager, copy my ONE line into the refers to box. I did NOT mean print
header, I meant the first row to print which is found by matching your
text and the last row by matching "*", 0. You will NOT have to enter
any row numbers. Excel will do it for you. So, revised. Please EDIT to
your choice of TEXT and change the column NUMBER at the end. By chance
did you attend Texas A & M?
copy this to the refers to: box
=OFFSET(INDIRECT("sheet2!$a$"&MATCH("first row to print text",Sheet2!
$A:$A,0)),0,0,MATCH("*",Sheet2!$A:$A,-1)-MATCH("first row to print
text",Sheet2!$A:$A,0)+1,3)

Just in case that is too difficult for you, here is a macro to do it
for you.
Sub NamePrintAreainSheetTwo()
With ActiveWorkbook.Worksheets("Sheet2").Names("Print_Area")
.Name = "Print_Area"
.RefersToR1C1 = _
"=OFFSET(INDIRECT(""sheet2!$a$""&MATCH(""first row to print
text"",Sheet2!C1,0)),0,0,MATCH(""*"",Sheet2!C1,-1)-MATCH(""first row
to print text"",Sheet2!C1,0)+1,3)"
.Comment = ""
End With
End Sub

Please let us know how you make out...

 

[CellShocked]

Perhaps you may now understand WHY I offered to have you send me your
file.

And I may post a grab of my file to mediafire or the like. Just not the
file as is,so there would be a delay while I construct the 'sample'
workbook.

Always nice to mention excel version in the OP.

Yes, well... There was a time before the apps cost several hundred
dollars more than they are worth when everyone could have been expected
to be on the most recent version.

Since the price is so high, you guys now have to expand your window of
coverage. so yeah, I should have mentioned it. Another sad indicator
though. I *should* be able to define a name from the insert dialog
(ribbon) without bringing up the name manager from within the "formula"
ribbon.

I will continue tonight after I get back from work. Thanks, bro.

 

[Don Guillett]

  And I may post a grab of my file to mediafire or the like. Just not the
file as is,so there would be a delay while I construct the 'sample'
workbook.


  Yes, well...  There was a time before the apps cost several hundred
dollars more than they are worth when everyone could have been expected
to be on the most recent version.

  Since the price is so high, you guys now have to expand your window of
coverage.  so yeah, I should have mentioned it. Another sad indicator
though.  I *should* be able to define a name from the insert dialog
(ribbon) without bringing up the name manager from within the "formula"
ribbon.  

  I will continue tonight after I get back from work. Thanks, bro.

=======
I have some clients still using xl95. I have to design for THAT.
You can customize your quick access toolbar to add the name manager
icon
Send the file DIRECTLY to me if desired. If all else fails, just run
the macro I gave you. You didn't answer the question, "Did you attend
Texas A & M" .. Us old folks do not use the term "bro"

 

[CellShocked]

=======
I have some clients still using xl95. I have to design for THAT.
You can customize your quick access toolbar to add the name manager
icon
Send the file DIRECTLY to me if desired. If all else fails, just run
the macro I gave you. You didn't answer the question, "Did you attend
Texas A & M" .. Us old folks do not use the term "bro"

I did not school in Texas. I did add the name manager to my Qa
Toolbar. I am going to work on it further this evening.