R
Richard
Once i have defined a named range, how do delete it, or
extend it to include more cells?
Thanks in advance
Richard
extend it to include more cells?
Thanks in advance
Richard
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Don Guillett
insert>name>define>select it>delete
to extend use f2 to edit but better to
=offset($a$1,0,0,counta($a:$a),1)
to define for self adjustment.
to extend use f2 to edit but better to
=offset($a$1,0,0,counta($a:$a),1)
to define for self adjustment.
R
RagDyer
Can you tell me Don, what's the reason for that last "1" in your formula?
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
insert>name>define>select it>delete
to extend use f2 to edit but better to
=offset($a$1,0,0,counta($a:$a),1)
to define for self adjustment.
--
Regards,
RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------
insert>name>define>select it>delete
to extend use f2 to edit but better to
=offset($a$1,0,0,counta($a:$a),1)
to define for self adjustment.
T
TKT-Tang
Mr. Don Guillet,
What is the maximum number of characters acceptable entered into
"Refers to : Control Box ?"
Regards.
What is the maximum number of characters acceptable entered into
"Refers to : Control Box ?"
Regards.
D
Don Guillett
I realize it is not necessary for only ONE column but tend to use it for
offset formula clarification.
offset formula clarification.
D
Don Guillett
Dunno. Never needed more than a few to define the name. My guess is 256.
My question to you is, why?
My question to you is, why?
G
Gord Dibben
TKT
255 characters maximum in the "Refers to:" dialog box.
Gord Dibben Excel MVP
255 characters maximum in the "Refers to:" dialog box.
Gord Dibben Excel MVP
R
RagDyer
I seem to have a mental block when it comes to Offset, and most times need
the syntax in front of me.
--
Regards,
RD
the syntax in front of me.
--
Regards,
RD
D
Don Guillett
Me too. So, I look at this
OFFSET(reference,rows,cols,height,width)
Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET
returns the #VALUE! error value.
Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).
Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).
Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.
Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.
OFFSET(reference,rows,cols,height,width)
Reference is the reference from which you want to base the offset.
Reference must refer to a cell or range of adjacent cells; otherwise, OFFSET
returns the #VALUE! error value.
Rows is the number of rows, up or down, that you want the upper-left cell
to refer to. Using 5 as the rows argument specifies that the upper-left cell
in the reference is five rows below reference. Rows can be positive (which
means below the starting reference) or negative (which means above the
starting reference).
Cols is the number of columns, to the left or right, that you want the
upper-left cell of the result to refer to. Using 5 as the cols argument
specifies that the upper-left cell in the reference is five columns to the
right of reference. Cols can be positive (which means to the right of the
starting reference) or negative (which means to the left of the starting
reference).
Height is the height, in number of rows, that you want the returned
reference to be. Height must be a positive number.
Width is the width, in number of columns, that you want the returned
reference to be. Width must be a positive number.
T
TKT-Tang
Mr. Don Guillett and Mr. Gord Guillet,
Thank you for responding to my query.
I have looked at the Excel specifications in the Help file and the
"Refers to :" control box is not stated thereof.
I could have experimented to verify the acceptable length of
characters entered into the control box ; but, obviously, it's easier
to ask.
Time and again, the strings entered into the control box exceeded the
limit.
Admittedly, constructing worksheet formulae by using the Define Name
dialog box is not all that readily manageable. But, that's another
topic of discussion saved for another day.
Regards.
Thank you for responding to my query.
I have looked at the Excel specifications in the Help file and the
"Refers to :" control box is not stated thereof.
I could have experimented to verify the acceptable length of
characters entered into the control box ; but, obviously, it's easier
to ask.
Time and again, the strings entered into the control box exceeded the
limit.
Admittedly, constructing worksheet formulae by using the Define Name
dialog box is not all that readily manageable. But, that's another
topic of discussion saved for another day.
Regards.
D
Don Guillett
Again, if you post your LONG formula perhaps a suggestion could be
forthcoming.
forthcoming.
T
TKT-Tang
Mr. Don Guillett,
Thank you for responding to my query.
Please refer to,
http://groups.google.com.sg/groups?...el.worksheetfunctions&c2coff=1&sa=G&scoring=d
Regards.
Thank you for responding to my query.
Please refer to,
http://groups.google.com.sg/groups?...el.worksheetfunctions&c2coff=1&sa=G&scoring=d
Regards.
D
Don Guillett
You still didn't post the formula but maybe using the F2 key will help you
edit. Try it.
edit. Try it.
T
TKT-Tang
Re – Deleting or editing Named Ranges
Mr. Don Guillet,
Thank you for responding to my query.
1. After much struggle, the following worksheet formula produces the
results besought :-
1a. {=IF(ROWS($BD$54:$BD54)>ROUND(UniqueLineCriteria-1,0),CHAR(149),
"Line no. "&TEXT(
INDEX($R$54:$R$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1)),"000")&"-"&
INDEX($S$54:$S$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1))&"-"&
INDEX($T$54:$T$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1))&"-"&
INDEX($U$54:$U$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1)))}
2. The following Named Formulae are Inserted with the Define Name
dialog box :-
2a. CountGeneral = ROW()-ROW('Sketches&Photos'!$BF$54)+1-
COUNTBLANK(OFFSET('Sketches&Photos'!$BF$54,0,0,ROW()-
ROW('Sketches&Photos'!$BF$54)+1,1))
2b. CountifBlank = COUNTIF(LineCode,"<="&LineCode)+
COUNTBLANK('Sketches&Photos'!$BE$54:$BE$202)
2c. FrequencyCountif = IF(FREQUENCY(COUNTIF(LineCode,"<="&LineCode),
COUNTIF(LineCode,"<="&LineCode))>0,
CountifBlank,ROWS('Sketches&Photos'!$BE$54:$BE$202))
2d. IndexMatchSmall = INDEX(LineCode,MATCH(SMALL(
IF(ISBLANK(LineCodePlus1),ROWS('Sketches&Photos'!$BE$54:$BE$202),
IF(LineCodePlus1=CHAR(149),ROWS('Sketches&Photos'!$BE$54:$BE$202),FrequencyCountif)),
CountGeneral),CountifBlank,0))
2e. LineCode = 'Sketches&Photos'!$BE$54:$BE$202
2f. LineCodePlus1 = OFFSET(LineCode,0,0,ROWS(LineCode)+1,1)
2g. UniqueLineCriteria = SUM(
IF(COUNTIF('Sketches&Photos'!$BE$54:$BE$202,
'Sketches&Photos'!$BE$54:$BE$202)>0,
1/COUNTIF('Sketches&Photos'!$BE$54:$BE$202,
'Sketches&Photos'!$BE$54:$BE$202),0))
3. Remarks :-
3a. The name of the worksheet is 'Sketches&Photos'.
3b. If the worksheet formula is to be applied in another worksheet or
another workbook, it would be a task tussle with respect to the
editing of tabs and references (recalling which is intended for what
under the circumstances of involuntary memory lapse) required to suit.
And there, amidst the list in the Define Name dialog box, many more
other formulae roll up and down not-so-unison-ly. That's the
formidable obstacle to the application of the formula in other
instances and therefore a custom Name-Manager would be helpful.
3c. In retrospect, there's an inevitable avalanche of resentment
stemmed from such pursuit and entrapment thereof ; nevertheless, this
is an attempt of the first step to get it off my chest. Neutrally
speaking, this query is not intent upon manifesting the convolution of
things but, bracing in the state of being snowballed and cocooned.
3d. The worksheet formula was devised to order the entries in a range
of worksheet cells.
4. Regards.
Mr. Don Guillet,
Thank you for responding to my query.
1. After much struggle, the following worksheet formula produces the
results besought :-
1a. {=IF(ROWS($BD$54:$BD54)>ROUND(UniqueLineCriteria-1,0),CHAR(149),
"Line no. "&TEXT(
INDEX($R$54:$R$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1)),"000")&"-"&
INDEX($S$54:$S$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1))&"-"&
INDEX($T$54:$T$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1))&"-"&
INDEX($U$54:$U$202,MAX((IndexMatchSmall=$BE$54:$BE$202)*
ROW($BE$54:$BE$202)-ROW($BE$54)+1)))}
2. The following Named Formulae are Inserted with the Define Name
dialog box :-
2a. CountGeneral = ROW()-ROW('Sketches&Photos'!$BF$54)+1-
COUNTBLANK(OFFSET('Sketches&Photos'!$BF$54,0,0,ROW()-
ROW('Sketches&Photos'!$BF$54)+1,1))
2b. CountifBlank = COUNTIF(LineCode,"<="&LineCode)+
COUNTBLANK('Sketches&Photos'!$BE$54:$BE$202)
2c. FrequencyCountif = IF(FREQUENCY(COUNTIF(LineCode,"<="&LineCode),
COUNTIF(LineCode,"<="&LineCode))>0,
CountifBlank,ROWS('Sketches&Photos'!$BE$54:$BE$202))
2d. IndexMatchSmall = INDEX(LineCode,MATCH(SMALL(
IF(ISBLANK(LineCodePlus1),ROWS('Sketches&Photos'!$BE$54:$BE$202),
IF(LineCodePlus1=CHAR(149),ROWS('Sketches&Photos'!$BE$54:$BE$202),FrequencyCountif)),
CountGeneral),CountifBlank,0))
2e. LineCode = 'Sketches&Photos'!$BE$54:$BE$202
2f. LineCodePlus1 = OFFSET(LineCode,0,0,ROWS(LineCode)+1,1)
2g. UniqueLineCriteria = SUM(
IF(COUNTIF('Sketches&Photos'!$BE$54:$BE$202,
'Sketches&Photos'!$BE$54:$BE$202)>0,
1/COUNTIF('Sketches&Photos'!$BE$54:$BE$202,
'Sketches&Photos'!$BE$54:$BE$202),0))
3. Remarks :-
3a. The name of the worksheet is 'Sketches&Photos'.
3b. If the worksheet formula is to be applied in another worksheet or
another workbook, it would be a task tussle with respect to the
editing of tabs and references (recalling which is intended for what
under the circumstances of involuntary memory lapse) required to suit.
And there, amidst the list in the Define Name dialog box, many more
other formulae roll up and down not-so-unison-ly. That's the
formidable obstacle to the application of the formula in other
instances and therefore a custom Name-Manager would be helpful.
3c. In retrospect, there's an inevitable avalanche of resentment
stemmed from such pursuit and entrapment thereof ; nevertheless, this
is an attempt of the first step to get it off my chest. Neutrally
speaking, this query is not intent upon manifesting the convolution of
things but, bracing in the state of being snowballed and cocooned.
3d. The worksheet formula was devised to order the entries in a range
of worksheet cells.
4. Regards.
J
Jan Karel Pieterse
Hi TKT-Tang,
The limit is somewhere around 240 characters.
BTW: If you use defined names a lot, consider downloading the
Name manager (by Charles Williams, Matthew Henson and
myself) from:
www.jkp-ads.com
or
www.bmsltd.ie/mvp
or from:
www.decisionmodels.com/downloads.htm
Regards,
Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
I could have experimented to verify the acceptable length of
characters entered into the control box ; but, obviously, it's easier
to ask.
Time and again, the strings entered into the control box exceeded the
limit.
The limit is somewhere around 240 characters.
BTW: If you use defined names a lot, consider downloading the
Name manager (by Charles Williams, Matthew Henson and
myself) from:
www.jkp-ads.com
or
www.bmsltd.ie/mvp
or from:
www.decisionmodels.com/downloads.htm
Regards,
Jan Karel Pieterse
Excel MVP
www.jkp-ads.com