Dynamic Range for non contiguous range

[prkhan56]

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

[Bob Phillips]

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
>

[prkhan56]

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

[Bob Phillips]

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
>

[prkhan56]

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


Rashid

[Harlan Grove]

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

[Bob Phillips]

"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!

[prkhan56]

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

[Bob Phillips]

=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

[prkhan56]

Hello Bob,
I tried your suggestion as follows:
Range Name PCID
=OFFSET(e1,,,MAX(IF(ISBLANK(E1:E65535),0,ROW(E1:E65535))))
But many problems have cropped up....I will explain it here:
1) Cell A2 = KMC, B2 = 111, C2 = DT, D2 = ASM, E2 = A2&B2&C2&D2
which shows KMC111DTASM in this case... which when I used it with the
formula =OFFSET(PC!$E$2,0,0,COUNTA(PC!$E:$E),COUNTA(PC!$2:$2)) shows
me the PCID in a drop down box but as I said in my previous post it
does not cover the blank rows in between.
2) When I use your formula for PCID as
=OFFSET(E2,,,MAX(IF(ISBLANK(E2:E65535),0,ROW(E2:E65535)))) it shows me
only DT from Cell C5 to C9 in the drop down box from the Sheet used for
the Vlookups. That is only 5 values are shown in the drop down box...I
tried with many different values in Cells C5 to C9 and it shows
accordingly...

What is wrong with the formula?

My requirement is to have a drop down list with PCID (which should be
dynamic to include blank rows.

As you are on it...one more thing I wish to ask you was that if I want
to make a dynamic Square Block... which will include my last used Row
as well as the last used column then what would be the formula for
that..
Something like...
=OFFSET(e1,,,MAX(IF(ISBLANK(E1:E65535),0,COLUMN(??:?????))))

Hope you would help me out please.

Thanks once again for your time and help.

Rashid Khan

[Domenic]

Try...

=OFFSET(PC!$E$2,0,0,LOOKUP(2,1/(1-ISBLANK(PC!$E$2:$E$65536)),ROW(PC!$E$2:
$E$65536)-ROW(PC!$E$2)+1))

and

=OFFSET(PC!$E$2,0,0,LOOKUP(2,1/(1-ISBLANK(PC!$E$2:$E$65536)),ROW(PC!$E$2:
$E$65536)-ROW(PC!$E$2)+1),LOOKUP(2,1/(1-ISBLANK(PC!$E$2:$IV$2)),COLUMN(PC
!$E$2:$IV$2)-COLUMN(PC!$E$2)+1))

Hope this helps!

In article <[email protected]>,
"prkhan56" <[email protected]> wrote:

> Hello Bob,
> I tried your suggestion as follows:
> Range Name PCID
> =OFFSET(e1,,,MAX(IF(ISBLANK(E1:E65535),0,ROW(E1:E65535))))
> But many problems have cropped up....I will explain it here:
> 1) Cell A2 = KMC, B2 = 111, C2 = DT, D2 = ASM, E2 = A2&B2&C2&D2
> which shows KMC111DTASM in this case... which when I used it with the
> formula =OFFSET(PC!$E$2,0,0,COUNTA(PC!$E:$E),COUNTA(PC!$2:$2)) shows
> me the PCID in a drop down box but as I said in my previous post it
> does not cover the blank rows in between.
> 2) When I use your formula for PCID as
> =OFFSET(E2,,,MAX(IF(ISBLANK(E2:E65535),0,ROW(E2:E65535)))) it shows me
> only DT from Cell C5 to C9 in the drop down box from the Sheet used for
> the Vlookups. That is only 5 values are shown in the drop down box...I
> tried with many different values in Cells C5 to C9 and it shows
> accordingly...
>
> What is wrong with the formula?
>
> My requirement is to have a drop down list with PCID (which should be
> dynamic to include blank rows.
>
> As you are on it...one more thing I wish to ask you was that if I want
> to make a dynamic Square Block... which will include my last used Row
> as well as the last used column then what would be the formula for
> that..
> Something like...
> =OFFSET(e1,,,MAX(IF(ISBLANK(E1:E65535),0,COLUMN(??:?????))))
>
> Hope you would help me out please.
>
> Thanks once again for your time and help.
>
> Rashid Khan

[prkhan56]

Hello Bob,
Thanks .. your formula works fine.

FYI, it is however very slow.. it shows Calculating 0%......to
100%...at the left bottom screen..

Can this be also fixed.. as a temporary measure I have reduced the
number of rows and columns in the formula

Any suggestions?

Thanks once again

Rashid Khan