John,

I am not sure I am getting this. If I have say 4 entries in employyeNames,
and only 3 in Address3, and that 4th name is in d44, I get 'No Address in
Timpoint', not #REF.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
...
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)