Dynamic Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rjameel67@hotmail.com - 08 Jun 2006 05:26 GMT

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

Reply to this Message

Biff - 08 Jun 2006 06:10 GMT

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

> Hi All,
>
[quoted text clipped - 14 lines]
>
> Rashid

Reply to this Message