Bob

The formula below from Frank will work in that instance, it doesn't however
work (and you get #Ref!) if only 2 Address3 fields are completed, thus the
Range Name for Address3 expands to the last populate cell in the Address3
column + 1. So if you have only 2 Address3 fields populated you get a #Ref!
not "No Address in Timepoint"

I'll send you my spreadsheet if you like

Thanks



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