Dynamic Range for non contiguous range

Hello All,

I am using Office XP and use the following formula for the dynamic
range

=OFFSET(PC!$E$2,0,0,COUNTA(PC!$E:$E),COUNTA(PC!$2:$2))

the above works fine if there is no blank rows.


Can this be modified to included blank rows which appear in between..
or to include the last row used

Can anybody give me a clue

TIA

Rashid

You could try

=OFFSET(A1,,,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))

but make sure that any formula that you use it in is an array formula.

--
HTH

Bob Phillips

"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> Hello All,
>
> I am using Office XP and use the following formula for the dynamic
> range
>
> =OFFSET(PC!$E$2,0,0,COUNTA(PC!$E:$E),COUNTA(PC!$2:$2))
>
> the above works fine if there is no blank rows.
>
>
> Can this be modified to included blank rows which appear in between..
> or to include the last row used
>
> Can anybody give me a clue
>
> TIA
>
> Rashid
>

Hello Bob,
Thanks for your prompt reply.
But I cannot use it as it says "Array Formulas are not Valid in Merged
Cells"

Any suggestions.

Rashid

Do you have to use merged cells, they are more trouble than they are worth.

You could use Format>Cell>Alignment and the Center Across Selection option.

--
HTH

Bob Phillips

"prkhan56" <[email protected]> wrote in message
news:[email protected]...
> Hello Bob,
> Thanks for your prompt reply.
> But I cannot use it as it says "Array Formulas are not Valid in Merged
> Cells"
>
> Any suggestions.
>
> Rashid
>

Thanks I would give it a try and get back to u.


Rashid

"Bob Phillips" <[email protected]> wrote...
>You could try
>
>=OFFSET(A1,,,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))
>
>but make sure that any formula that you use it in is an array formula.
....

Or replace the MAX call with

LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))

which doesn't require array entry.

"Harlan Grove" <[email protected]> wrote in message
news:%[email protected]...
> "Bob Phillips" <[email protected]> wrote...
> >You could try
> >
> >=OFFSET(A1,,,MAX(IF(ISBLANK(A1:A65535),0,ROW(A1:A65535))))
> >
> >but make sure that any formula that you use it in is an array formula.
> ...
>
> Or replace the MAX call with
>
> LOOKUP(2,1/(1-ISBLANK(A1:A65535)),ROW(A1:A65535))
>
> which doesn't require array entry.

better!

Hello Bob
I tried your suggestion but it does not work ...it is giving me
NA and REF errors

Following formula is used in range name PcData which I changed to suit
my requirement

=3DOFFSET(e1,,,MAX(IF(ISBLANK(E1=AC:E65535),0,ROW(E1:E65535))))

Following is the Vlookup formula where I am using PcData
=3DIF(ISBLANK(VLOOKUP($C$5,PcData,6,FALSE)),"",(VLOOKUP($C$5,PcData,6,FALSE=
)))

I also tried the Vlookup formula as an array formula, as per your
suggestion .. but still no luck.

I also tried the following .. but still gives me NA and REF errors
=3DOFFSET(E1,,,LOOKUP(2,1/(1-ISBLANK(E1:E65535)),ROW(E1:E65535))


Am I doing something wrong?

Thanks=20

Rashid Khan

=OFFSET(e1,,,MAX(IF(ISBLANK(E1¬:E65535),0,ROW(E1:E65535))))

should be

=OFFSET(e1,,,MAX(IF(ISBLANK(E1:E65535),0,ROW(E1:E65535))))

and

=IF(ISNA(VLOOKUP($C$5,PcData,6,FALSE)),"",(VLOOKUP($C$5,PcData,6,FALSE)))

and make this an array formula, that is commit with Ctrl-SHift-Enter

HTH

Bob

"prkhan56" <[email protected]> wrote in message
news:[email protected]...
Hello Bob
I tried your suggestion but it does not work ...it is giving me
NA and REF errors

Following formula is used in range name PcData which I changed to suit
my requirement

=OFFSET(e1,,,MAX(IF(ISBLANK(E1¬:E65535),0,ROW(E1:E65535))))

Following is the Vlookup formula where I am using PcData
=IF(ISBLANK(VLOOKUP($C$5,PcData,6,FALSE)),"",(VLOOKUP($C$5,PcData,6,FALSE)))

I also tried the Vlookup formula as an array formula, as per your
suggestion .. but still no luck.

I also tried the following .. but still gives me NA and REF errors
=OFFSET(E1,,,LOOKUP(2,1/(1-ISBLANK(E1:E65535)),ROW(E1:E65535))


Am I doing something wrong?

Thanks

Rashid Khan