# clarify named range (=OFFSET/=MATCH)

#### buz

##### Board Regular
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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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.

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

So to name another range on another sheet i would choose names other than Drange and End?

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

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?

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

Replies
8
Views
1K
Replies
3
Views
189
Replies
5
Views
220
Replies
0
Views
519
Replies
4
Views
519

1,219,035
Messages
6,145,905
Members
450,654
Latest member
lorento

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

### Which adblocker are you using?

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

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