Dynamic Range

[rjameel67]

Hi All,

I have the following two dynamic ranges

=OFFSET(PBR!$A$1,0,0,COUNTA(PBR!$A:$A),1)
=OFFSET(PBR!$A$1,0,0,COUNTA(PBR!$A:$A),2)
Note: there will be two ranges
namecount & namelist

The formula which would be using the above range is
=IF(MAX(namecount)<ROW(1:1),"",VLOOKUP(ROW(1:1),namelist,2))


Big question!!!, can I join these ranges together in to one statement.
if so than how ???

Thansks,

Rashid

 

[Biff]

Hi!

If I follow what you want:

Named range: List
Refers to: =OFFSET(PBR!$A$1:$B$1,,,COUNTA(PBR!$A:$A))

Then:

=IF(MAX(INDEX(List,,1))<ROW(1:1),"",VLOOKUP(ROW(1:1),List,2))

If you are drag copying down this is a little more robust:

=IF(MAX(INDEX(List,,1))<ROWS($1:1),"",VLOOKUP(ROWS($1:1),List,2))

Biff