On Oct 25, 6:46*am, CellShocked
<cellshoc...@thecellvalueattheendofthespreadsheet. org wrote:
On Mon, 24 Oct 2011 05:35:40 -0700 (PDT), Don Guillett

wrote:
Insertnamedefinename 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.

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_A rea")
.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...