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)

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)

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)

Yeah do,

bob dot phillips at tiscali dot co dot uk

--

HTH

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


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

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)

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)

You are welcome. Glad to be of help.

--
Regards,

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

In article ,
says...
Tushar

Thanks for that, thats exactly what I needed

Rgds

John