|
Dynamic Ranges Q
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) |
Dynamic Ranges Q
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) |
Dynamic Ranges Q
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) |
Dynamic Ranges Q
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) |
Dynamic Ranges Q
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) |
Dynamic Ranges Q
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 |
| All times are GMT +1. The time now is 06:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com