Dynamic Range for non contiguous range

  • Thread starter Thread starter prkhan56
  • Start date Start date
P

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
 
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.
 
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.
 
Bob Phillips said:
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.
Click to expand...
....

Or replace the MAX call with

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

which doesn't require array entry.
 
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
 
=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

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
 
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
 
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!
 
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
 
Back
Top