clarify named range (=OFFSET/=MATCH)

buz

Board Regular
Joined
May 30, 2002
Messages
230
Drange w/ refers to is =OFFSET(Hrs!$A$2,0,0,End-1,4)

End w/ refers to is =MATCH(9.9999999E+307,Hrs!$D:$D)

In the Drange I see the sheet/row/col but loose it with End-1,4). What does End-1,4 refer to? Ibelieve the 'D' in Drange refers to col D on sheet hrs?????

In the =MATCH the sheet/range is understood, but what does the 9.9999999+307 refer to?

The point is I would like to name some more ranges using the OFFSET/MATCH routine. I can imagine for the Drange using perhaps another col heading on the other sheet like 'Erange refers to =OFFSET(SHEET!$A$2,0,0,End-1,4). Then it's a matter of the End part - how End was choosen for the =MATCH and what another good =MATCH name would be.

You see, it would help if I understood how things inter-relate.

Looks like a strange question to me - but hey - I gotta ask.

tfyh
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
The End name returns the last row with a number in column D of sheet Hrs.

Then, the DRange name, "stands" in Hrs!A2, extends down End-1 rows (Height), and to the left 4 columns (Width).

The D in DRange is just the name, you can change that to whatever you want.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
In the =MATCH the sheet/range is understood, but what does the 9.9999999+307 refer to?

9.9999999...+307 is for the largest number that Excel can handle ... in the match function it is intended to pick up the row number of the last entry in column D

Regards!

Yogi
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
So to name another range on another sheet i would choose names other than Drange and End?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

On 2002-10-04 00:40, buz wrote:
So to name another range on another sheet i would choose names other than Drange and End?

Yes. Suppose that we have 3 worksheets in Book1 and the following current data areas are frequently changing--that is, expanding or crimping:

Sheet1: A1:E300

Sheet2: F60:H2000

Sheet3: B2:J659

You can define appropriate names by means of dynamic formulas (usually with OFFSET) for each of the above ranges...

The intermediate steps can be put in a separate worksheet which I often name Admin.

In Admin you compute with respect to each range the first and last cells for each range. We use here MATCH, not COUNT or COUNTA. Two rules:

(1) If a range has a numeric column (or row), which is ideal, we use:

=MATCH(BigNum,x!ColRef)

where BigNum is 9.99999999999999E+307 and ColRef (e.g., C:C) is of numeric type and x is the sheetname. Thus we pick out a numeric column from the range of interest to compute the current maximum extent of the range.

(2) If a range is all text, we use:

=MATCH(REPT("z",90),x!ColRef)

where REPT("z",90) plays the same role as BigNum does.

(3) If a range is mixed, we use:

=MAX(MATCH(BigNum,x!ColRef),MATCH(REPT("z",90),x!ColRef))

Since 9.99999999999999E+307 is an important number and hard to type when needed, we define a name for it: BigNum.

See:

http://www.mrexcel.com/board/viewtopic.php?topic=16207&forum=2&7

http://www.mrexcel.com/board/viewtopic.php?topic=24122&forum=2

Aladin
 

buz

Board Regular
Joined
May 30, 2002
Messages
230
Then, the DRange name, "stands" in Hrs!A2, extends down End-1 rows (Height), and to the left 4 columns (Width).

Your description of the width??????? is it to the 'left' four columns or to the right?

Seems like it may be just a typo - but guesing if I changed it from

=OFFSET(Hrs!$A$2,0,0,End-1,4)

to

=OFFSET(Hrs!$A$2,0,0,End-1,6)

then it would be picking up 6 columns?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
On 2002-10-08 01:13, buz wrote:
Then, the DRange name, "stands" in Hrs!A2, extends down End-1 rows (Height), and to the left 4 columns (Width).

Your description of the width??????? is it to the 'left' four columns or to the right?

Seems like it may be just a typo - but guesing if I changed it from

=OFFSET(Hrs!$A$2,0,0,End-1,4)

to

=OFFSET(Hrs!$A$2,0,0,End-1,6)

then it would be picking up 6 columns?

To the right...

The last argument, here 6, indicates width, put otherwise, the number of columns the deined range consists of.

Thus the defined range runs here from column A to F inclusive.
This message was edited by Aladin Akyurek on 2002-10-08 01:59
 

Forum statistics

Threads
1,144,329
Messages
5,723,736
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top