# Dynamic Range

#### hsk

##### Well-known Member
I am trying to use dynamic range ...

I inserted a new range Dynam =
=OFFSET(Sheet7!\$C\$36,0,0,MATCH(9.9999E+307,Sheet7!\$C:\$C)-1)

and using the named range Dynam in match formula

=MATCH(B36,Dynam,0)

I have used dynamic ranges in other files and those are working perfectly.

Not sure why it's not working for the above case .....
I have checked ....
the match formula in OFFSET shud return number of filled cells in column C
For some reason it is not returning correct value ....

Any clue ??

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### jim may

##### Well-known Member
Seems like its the CountA() Versus Match() that you need.
Can't tell from what little you provided..

#### hsk

##### Well-known Member
Got the error ...............
MATCH(9.9999E+307,\$A:\$A) ....
will give error if there is not a single numeric value in column A,

Can't tell from what little you provided..

Following are details,

I am using OFFSET formula for dynamic range ..... where i expect the MATCH(...) should return the row number of last filled cell in that column.

But what i learnt is, the formula
=MATCH(9.9999E+307,\$A:\$A) ... will return row of last "Number" in column A

Where formula
=MATCH("*",\$A:\$A,-1) ... will return row of last char in column A

Now if i want to get last row in column A, char/number anything , then i need to use,

= LOOKUP(9.999E+307,CHOOSE({1,2,3},MATCH("*",\$A:\$A,-1),MATCH(9.9999E+307,\$A:\$A),MAX(MATCH("*",\$A:\$A,-1),MATCH(9.9999E+307,\$A:\$A))))

Hope that clears it more !!!

Thanks,
hsk

##### MrExcel MVP
Lets define BigNum as referring to:

9.99999999999999E+307

That settled...

MATCH(BigNum,X)

where X is either a definite range or a whole column/row reference.

[1] If one wants to invoke the foregoing in order to define a dynamic range, X must be numeric.

Dynam, which you mention in your original post and which is defined as referring to

=OFFSET(Sheet7!\$C\$36,0,0,MATCH(9.9999E+307,Sheet7!\$C:\$C)-1)

should be formulated a bit differently, assuming that the range in C is numeric:

Define Size as

=MATCH(BigNum,Sheet7!\$C:\$C)-ROW(Sheet7!\$C\$36)+1

Then define Dynam as:

=OFFSET(Sheet7!\$C\$36,0,0,Size)

[2] What if the range in column C consists of solely text, including "", i.e., formula blanks and there isn't any other range to use which is numeric...

Define BigText as:

=REPT("z",255)

Then define Size as:

=MATCH(BigText,Sheet7!\$C:\$C)-ROW(Sheet7!\$C\$36)+1

Dynam is than defined as before.

[3] What if the range in column C consists of text and numbers, including "", i.e., formula blanks and there isn't any numeric range to use...

Then defining Size as:
Code:
``````=LOOKUP(BigNum,CHOOSE({1,2,3},
MATCH(BigText,Sheet7!\$C:\$C),
MATCH(BigNum,Sheet7!\$C:\$C),
MAX(MATCH(BigText,Sheet7!\$C:\$C),MATCH(BigNum,Sheet7!\$C:\$C))))-
ROW(Sheet1!\$C\$36)+1``````

is a good idea.

[4] If the range in column C can contain any value, COUNTA would be an appropriate choice if the range does not contain any in-between empty cells:

The definition of Size changes to:

=COUNTA(Sheet7!\$C:\$C)-ROW(Sheet7!\$C\$36)+1

[5] If the range in column C can contain any value and there is at least an in-between empty cell, the definition of Size becomes a bit harder and expensive...
Code:
``````=IF(1-ISBLANK(INDEX(Sheet7!\$C:\$C,ROWS(Sheet7!\$C:\$C))),
ROWS(Sheet7!\$C:\$C),
MATCH(BigNum,1/(1-ISBLANK(OFFSET(INDEX(Sheet7!\$C:\$C,1),0,0,
ROWS(Sheet7!\$C:\$C)-1)))))-ROW(Sheet7!\$C\$36)+1``````

Note. The idiom

MATCH("*",X,-1)

can be better avoided in this context. It would yield a wrong result with:

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>q</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:str="" x:fmla='=""'> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>w</TD></TR></TBODY></TABLE>
where the cell between that of "a" and that of "w" is a formula blank, i.e., "".

Got the error ...............
MATCH(9.9999E+307,\$A:\$A) ....
will give error if there is not a single numeric value in column A,

Following are details,

I am using OFFSET formula for dynamic range ..... where i expect the MATCH(...) should return the row number of last filled cell in that column.

But what i learnt is, the formula
=MATCH(9.9999E+307,\$A:\$A) ... will return row of last "Number" in column A

Where formula
=MATCH("*",\$A:\$A,-1) ... will return row of last char in column A

Now if i want to get last row in column A, char/number anything , then i need to use,

= LOOKUP(9.999E+307,CHOOSE({1,2,3},MATCH("*",\$A:\$A,-1),MATCH(9.9999E+307,\$A:\$A),MAX(MATCH("*",\$A:\$A,-1),MATCH(9.9999E+307,\$A:\$A))))

Hope that clears it more !!!

Thanks,
hsk

#### hsk

##### Well-known Member

I got more than i was looking for

This is gr8 stuff !!!!!!!

Thanks a ton !!!!

Replies
0
Views
496
Replies
1
Views
89
Replies
0
Views
186
Replies
3
Views
308
Replies
0
Views
168

1,190,833
Messages
5,983,146
Members
439,825
Latest member
glen3265

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