aka_krakur
Active Member
- Joined
- Jan 31, 2006
- Messages
- 438
I am having difficulty creating a dynamic named range based off of a offset formula. The data on the worksheet changes based off a query refresh and I need the named ranges to change accordingly. I had someone help me write this formula :
defined name "dataul" refers to: =$W$25
defined name "data" refers to: =offset(dataul,0,0,counta($W:$W),1)
Okay, so I did a refresh on the data and the last row of data was 2055.
I looked at the defined name range for "data" and it went down to 2058 (which contains 3 rows of no data).
This messes up a formula I have because the formula doesn't like blanks. Unless someone can help me re-write the formula to search even blank data--not sure if it can be done.
Here's the formula with the dynamic named ranges in it:
defined name "dataul" refers to: =$W$25
defined name "data" refers to: =offset(dataul,0,0,counta($W:$W),1)
Okay, so I did a refresh on the data and the last row of data was 2055.
I looked at the defined name range for "data" and it went down to 2058 (which contains 3 rows of no data).
This messes up a formula I have because the formula doesn't like blanks. Unless someone can help me re-write the formula to search even blank data--not sure if it can be done.
Here's the formula with the dynamic named ranges in it:
Code:
=(SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(ChartData!$B$4:$E$4,'TEMPLATEProductFamily-ReportedCode-Test.xls'!data))+0,ROW(ReportableQuery!$W$25:INDEX('TEMPLATEProductFamily-ReportedCode-Test.xls'!data,COLUMNS(ChartData!$B$4:$E$4)))^0)>0),--(MMULT(--ISNUMBER(SEARCH(ChartData!$A$4,'TEMPLATEProductFamily-ReportedCode-Test.xls'!pf_data))+0,ROW(ReportableQuery!$L$25:INDEX('TEMPLATEProductFamily-ReportedCode-Test.xls'!pf_data,COLUMNS(ChartData!$A$4)))^0)>0),--('TEMPLATEProductFamily-ReportedCode-Test.xls'!date_data-DAY('TEMPLATEProductFamily-ReportedCode-Test.xls'!date_data)+1=ChartData!B$7)))