Tushar

Thanks for that, thats exactly what I needed

Rgds

John

"Tushar Mehta" wrote in message
...
In article ,
says...

How then can I create a Dynamic Range that will reference its range (say
for
eg Address Line 3) to the Length of the Range Name thats defined within
EmployeeNames (obviously they are in different columns). The logic here
is
that if there is no Employee there isn't a requirement to have an
Address3
line.

If one of the columns serves as the 'control' column, so to say, you
should create dynamic range formulas for each column that simply 'key
off' the control column. So, in your case, if payroll# is the control
column, I would use RateOfPay as =OFFSET(PayrollRng,0,1), AddrLine1 as
=OFFSET(PayrollRng,0,2), up to Address3 as =OFFSET(PayrollRng,0,4)

Of course PayrollRng would be what you already have, i.e.,
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

For more of this use see Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a problem concerning Dynamic Ranges, I have looked at the
Contextures
site and can't find a solution for me.

I wish to create a dynamic range for each of the columns that I pull
data
from an Access database. Depending on the location of business (i.e.
location = a different database) each column could be any length. I am
trying to pull master information for each employee such as, Payroll #;
Rate
of Pay, Address line 1, Address line 2, Address line 3 etc

The following code for a Dynamic Range taken from Contextures
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) causes a #Ref! problem
when
for example the data count in Address Line 3 (i.e. Address3) does not
equal
the data count in EmployeeNames which I am trying to Match against
Address
Line 3 via this formula

=IF(INDEX(Address3,MATCH(D44,EmployeeNames,0))=0," No Address in
Timepoint",INDEX(Address3,MATCH(D44,EmployeeNames, 0)))

How then can I create a Dynamic Range that will reference its range (say
for
eg Address Line 3) to the Length of the Range Name thats defined within
EmployeeNames (obviously they are in different columns). The logic here
is
that if there is no Employee there isn't a requirement to have an
Address3
line.

I received the folloing code for my Dynamic Range from Frank Kabel which
only holds true when you have data within the 2nd last row of the column
concerned


=OFFSET(Database!$J$2,0,0,SUMPRODUCT(MAX((Database !$J1:$J2000<"")*(ROW(Data
base!$J1:$J2000)))),1)