aka_krakur
Active Member
- Joined
- Jan 31, 2006
- Messages
- 438
I have one worksheet (ReportableQuery) that populates via a Microsoft Query.
Another worksheet (ChartData) has a cell with the following formula in cell B9 (through V9 respectively)
The formula itself works great (can't remember who to give kudos to but someone on this forum helped me with that one)
However, when I do a Data Refresh on the query on the RepoortableQuery worksheet, somehow the formula changes to $W$25:$W$25.
Note: the query is run by a number of Macros in VB that has a userform that collects the info for the query parameters, etc.
Part of that VB Macro deletes the data from the query 1st (Because there are non-query columns with formulas that have to be re-input)
So I'm guessing by the VB deleting the rows that were originally in this formula automatically updates the formula on the other worksheet to say "my range changed".
I tried writing a VB to repopulate these formulas on the ChartData worksheet (Cell B9) but I believe the formula's too long or something for VB.
Anyway, with all that said, any suggestions?
Another worksheet (ChartData) has a cell with the following formula in cell B9 (through V9 respectively)
Code:
=(SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH(ChartData!$B$4:$E$4,ReportableQuery!$W$25:$W$2052))+0,ROW(ReportableQuery!$W$25:INDEX(ReportableQuery!$W$25:$W$2052,COLUMNS(ChartData!$B$4:$E$4)))^0)>0),--(MMULT(--ISNUMBER(SEARCH(ChartData!$A$4,ReportableQuery!$L$25:$L$2052))+0,ROW(ReportableQuery!$L$25:INDEX(ReportableQuery!$L$25:$L$2052,COLUMNS(ChartData!$A$4)))^0)>0),--(ReportableQuery!$I$25:$I$2052-DAY(ReportableQuery!$I$25:$I$2052)+1=ChartData!C$7)))
The formula itself works great (can't remember who to give kudos to but someone on this forum helped me with that one)
However, when I do a Data Refresh on the query on the RepoortableQuery worksheet, somehow the formula changes to $W$25:$W$25.
Note: the query is run by a number of Macros in VB that has a userform that collects the info for the query parameters, etc.
Part of that VB Macro deletes the data from the query 1st (Because there are non-query columns with formulas that have to be re-input)
So I'm guessing by the VB deleting the rows that were originally in this formula automatically updates the formula on the other worksheet to say "my range changed".
I tried writing a VB to repopulate these formulas on the ChartData worksheet (Cell B9) but I believe the formula's too long or something for VB.
Anyway, with all that said, any suggestions?