clarify named range (=OFFSET/=MATCH)

[buz]

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

 

[Juan Pablo González]

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]

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]

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

 

[Aladin Akyurek]

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]

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]

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